lookups based on more than one column
 

Hello:

I'm not finding the LOOKUP, VLOOKUP, MATCH, and INDEX functions to be very
helpful for what I need.

I have a sheet of three columns. I want to return a value from the first
column based on corresponding values in the second AND third columns. In
other words, the value from column A is an exact match of what is in BOTH
column B AND C.

Is there a function in Excel that will allow for this?

Thanks!

childofthe1980s

lookups based on more than one column
 

Have you tried this?

Create another column to the left of the first column. Concatenate column 2
and column 3 and lookup your value in the new column. You need this new
column to the left of the original data as vlookup assumes the lookup value
is the left hand most column of the data table.


Hope this helps

Chris

"childothe1980s" wrote in message
...
Hello:

I'm not finding the LOOKUP, VLOOKUP, MATCH, and INDEX functions to be very
helpful for what I need.

I have a sheet of three columns. I want to return a value from the first
column based on corresponding values in the second AND third columns. In
other words, the value from column A is an exact match of what is in BOTH
column B AND C.

Is there a function in Excel that will allow for this?

Thanks!

childofthe1980s

lookups based on more than one column
 

The easiest way is to concatenate the contents of the two cells you want to
base your lookup on into a third column, and then use a VLookup on the new
column which contains both values. You can either use "=Concatenate(<cell1,
<cell2)" or "=<cell1 & <cell2" Where <Cell1 and <cell2 are the two cells
containing the two values you want to look up. You'll also have to do the
same to the values in your lookup table.

The other option would be to write your own custom function using Excel VBA
to manage the lookup for you. I suggest you use the first method if possible
- if that's no good, then we can look at the VBA function.

Ben.


"childothe1980s" wrote:

Hello:

I'm not finding the LOOKUP, VLOOKUP, MATCH, and INDEX functions to be very
helpful for what I need.

I have a sheet of three columns. I want to return a value from the first
column based on corresponding values in the second AND third columns. In
other words, the value from column A is an exact match of what is in BOTH
column B AND C.

Is there a function in Excel that will allow for this?

Thanks!

childofthe1980s

lookups based on more than one column
 

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.


childothe1980s wrote:

Hello:

I'm not finding the LOOKUP, VLOOKUP, MATCH, and INDEX functions to be very
helpful for what I need.

I have a sheet of three columns. I want to return a value from the first
column based on corresponding values in the second AND third columns. In
other words, the value from column A is an exact match of what is in BOTH
column B AND C.

Is there a function in Excel that will allow for this?

Thanks!

childofthe1980s

--

Dave Peterson

lookups based on more than one column
 

=Index(sheet1!R1:R30,Match(A1&B1&C1,Sheet1!F1:F30& Sheet1!M1:M30&Sheet1!Z1:Z3
0,0),1)

entered with Ctrl+Shift+Enter

--
Regards,
Tom Ogilvy


"childothe1980s" wrote in message
...
Hello:

I'm not finding the LOOKUP, VLOOKUP, MATCH, and INDEX functions to be very
helpful for what I need.

I have a sheet of three columns. I want to return a value from the first
column based on corresponding values in the second AND third columns. In
other words, the value from column A is an exact match of what is in BOTH
column B AND C.

Is there a function in Excel that will allow for this?

Thanks!

childofthe1980s