VLookUP with multiple reference columns

[Biff]

Is there a way to do a Vlookup that refers to more than the first column of
the table. If not, does anyone know an easy way to connvert a table
(multiple rows and multiple columns to a table with only two columns and
multiple rows) so that I can use the vlookup funtion?

 

[Franz Verga]

Hi Biff,

you can add an additional column to create a super index.

If, for example, you need to refer to column A, B and C, you can add a new
column before A and insert in the new column, that will be column A, the
following formula, for example in A2 and then drag down:

=B2&C2&D2

In this way you can refer to this new column.


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy


Nel post:[email protected],

 

[Shane Devenshire]

Hi,

We could use some more detail. Ranges, examples what have you.

You can use SUMPRODUCT in some cases, INDEX, MATCH, OFFSET or INDIRECT in
other cases. In 2007 you might be able to use SUMIFS or AVERAGEIFS or
COUNTIFS. Really without some detail we are in the dark.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Biff]

Hi Shane,

I realize that I wasn't very clear. I have a large table with data. I am
trying to find a value from the table, but the table is set up so that the
look up value could be found in more than one column. The return value can
also be found in more than one column, but the column to lookup and the
column to return the value is already determined based on the look up value.
It is another value in the table. I was hoping that I could use the
reference to the correct column in determining the table array and the column
index number. I would like to use one formual for all rows in the
spreadsheet without having do a seperate lookup depending on the array of
data that puts the look up value in the first column as the formula is
designed. Does my request make sure sense?

Biff

 

[Biff]

Hi Franz,

My limitation is that the V-look up function only refers to the first column
in the table array. How do I get it to refer to more than one column. So
if my table array is A2:F25, it wants to refer to only the first column which
is column when looking for the lookup value. How can I get the formula (or
some other formula) to refer to for example column A and B?

Thanks,
Biff

 

[barry houdini]

Hello Biff

What, exactly, determines the lookup column and/or the column to
return data from? Is the distance between these two columns fixed,
e.g. will the value to be returned always be in the column immediately
to the right of the lookup column (or 2 columns to the right) or is
that also variable?

 

[Biff]

The column is already determined and is stored in a seperate cell on the same
row as the look up reference. The distance between the two columens (look up
and return data) are the same distance in all cases.

Biff