lookup by multiple parameters

[NatashaS]

Hi,

I wondered if there is a function in Excel that allows you to look up a
value by two or more columns. (e.g. I want to ask for "column 1 = 1" AND
"column 2 = 5" to get the result "pears".

Column 1 Column 2 Column 3
1 5 apples
1 10 oranges
3 5 pears

 

[Peo Sjoblom]

=INDEX(C2:C10,MATCH(1,(A2:A10=1)*(B2:B10=5),0))

entered with ctrl + shift & enter

--


Regards,


Peo Sjoblom

 

[Gary Brown]

'===============================================
'Cell Formula using the OFFSET worksheet formula with multiple criteria to
' find a value in the same way that the VLOOKUP worksheet formula
' uses a single criteria to find a value.
'
'
'===============================================
'Forumla Example 1:
'Using actual values as the search criteria in EXAMPLE 1:
'{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17="John")*--(C12:C17="Nov")),0)}
' or
'Forumla Example 2:
'Using cell references as the search criteria in EXAMPLE 1:
'{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17=A6)*--(C12:C17=A7)),0)}
'
' - Remember to use CTRL-SHIFT-ENTER to make the formula an array
' showing the '{' and "}" at the beginning and ending of the formula
'
'Note: Just as VLOOKUP will give you the FIRST value it finds that meets it's
' criteria, this formul will give you the LAST value it finds that meets
' all of it's criteria.
'
'===============================================
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown