Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 57
Default VLookUP with multiple reference columns

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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 459
Default VLookUP with multiple reference columns

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 ,
Biff ha scritto:
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?




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 857
Default VLookUP with multiple reference columns

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" wrote in message
...
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?


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 57
Default VLookUP with multiple reference columns

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

"Shane Devenshire" wrote:

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" wrote in message
...
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?



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 57
Default VLookUP with multiple reference columns

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

"Franz Verga" wrote:

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 ,
Biff ha scritto:
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?







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 57
Default VLookUP with multiple reference columns

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?
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 57
Default VLookUP with multiple reference columns

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

"barry houdini" wrote:

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup with multiple reference Yanky Excel Worksheet Functions 1 December 11th 08 01:55 PM
Vlookup with Multiple like values in the reference column ckemtp Excel Worksheet Functions 3 July 3rd 08 03:34 AM
How do I vlookup multiple rows with same reference? Kathie C Excel Worksheet Functions 2 March 30th 08 07:09 PM
how do i reference multiple rows/columns with one function? NewUser13 New Users to Excel 1 March 4th 05 07:49 PM
how do i reference multiple rows/columns with one function? NewUser13 New Users to Excel 0 March 4th 05 07:45 PM


All times are GMT +1. The time now is 01:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"