Skip to main content
F
fward10-Marble
10-Marble
August 24, 2015
Solved

returning a range from vlookup

  • August 24, 2015
  • 2 replies
  • 1986 views

Dear friends,

I have problem trying to return a range from vlookup.  I have a table input which defines the steel gage number against the metric thickness.  The metric thickness is in column 0 and the gages in subsequent columns.  This variable is named "gages"

My calculation sheet is looking at the resistance of steel walls on a silo, and can have up to 35 different thickness in the one silo.  So I have a 1 x 35 input table in which the steel thickness is entered "Sidewall".  This is a unitless variable, which relates to the metric thickness of the steel.

All I want to do, using the vlookup function is to return the actual steel gage numbers for each of the Sidewall thicknesses.  Here is what I have entered:

wga[rn:vlookup(Sidewall[rn,gages,1)   rn is the range variable 1..35

What I was hoping was to get an output wga[rn, which gives me a 1 x 35 matrix of the steel gage numbers.  Instead, what I get is 35 row matrix of nested 1 x 1 matrices.  However if I enter wga[3 for instance, I get the value I want.

This is most vexing!  Can anyone help me get the actual value out as an array?

Best answer by StuartBruff

Frank Ward wrote:

Dear friends,

I have problem trying to return a range from vlookup.  I have a table input which defines the steel gage number against the metric thickness.  The metric thickness is in column 0 and the gages in subsequent columns.  This variable is named "gages"

My calculation sheet is looking at the resistance of steel walls on a silo, and can have up to 35 different thickness in the one silo.  So I have a 1 x 35 input table in which the steel thickness is entered "Sidewall".  This is a unitless variable, which relates to the metric thickness of the steel.

All I want to do, using the vlookup function is to return the actual steel gage numbers for each of the Sidewall thicknesses.  Here is what I have entered:

wga[rn:vlookup(Sidewall[rn,gages,1)   rn is the range variable 1..35

What I was hoping was to get an output wga[rn, which gives me a 1 x 35 matrix of the steel gage numbers.  Instead, what I get is 35 row matrix of nested 1 x 1 matrices.  However if I enter wga[3 for instance, I get the value I want.

The lookup functions return a vector not a scalar, hence you get exactly what you asked for -  a nested vector of vectors.  All you have to do is change your expression to

wga[rn:vlookup(Sidewall[rn,gages,1)[0   <-- add "[0" to the end.

Stuart

2 replies

S
StuartBruff23-Emerald VAnswer
23-Emerald V
August 24, 2015

Frank Ward wrote:

Dear friends,

I have problem trying to return a range from vlookup.  I have a table input which defines the steel gage number against the metric thickness.  The metric thickness is in column 0 and the gages in subsequent columns.  This variable is named "gages"

My calculation sheet is looking at the resistance of steel walls on a silo, and can have up to 35 different thickness in the one silo.  So I have a 1 x 35 input table in which the steel thickness is entered "Sidewall".  This is a unitless variable, which relates to the metric thickness of the steel.

All I want to do, using the vlookup function is to return the actual steel gage numbers for each of the Sidewall thicknesses.  Here is what I have entered:

wga[rn:vlookup(Sidewall[rn,gages,1)   rn is the range variable 1..35

What I was hoping was to get an output wga[rn, which gives me a 1 x 35 matrix of the steel gage numbers.  Instead, what I get is 35 row matrix of nested 1 x 1 matrices.  However if I enter wga[3 for instance, I get the value I want.

The lookup functions return a vector not a scalar, hence you get exactly what you asked for -  a nested vector of vectors.  All you have to do is change your expression to

wga[rn:vlookup(Sidewall[rn,gages,1)[0   <-- add "[0" to the end.

Stuart

F
fward10-MarbleAuthor
10-Marble
August 24, 2015

Stuart, you are a star!  Thank you so much.  I don't understand why it works, but am grateful that it does!

Many thanks

Frank

S
StuartBruff23-Emerald V
23-Emerald V
August 24, 2015

Frank Ward wrote:

Stuart, you are a star!  Thank you so much.  I don't understand why it works, but am grateful that it does!

Many thanks

No worries, Frank.  As for how it works, maybe this will help:

One thing I note is that you run your range variable from 1 to 35 rather than 0, and yet whilst the first entry in Sidewall is 0, there is a corresponding non-zero entry in gages.  Do you mean for this to be the case or would your worksheet be better expressed using ORIGIN = 1 rather than ORIGIN = 0?

Stuart

Terms & ConditionsAccessibility statement