Double lookup

[Dean]

I can't believe I don't know this. I'll call it a double, or nested, lookup
problem. Basically, based on getting a match in two columns, I want to read
the value in a 3rd column. For example...

Suppose I make a little table in EXCEL where the first column has the type
of fruit, say apple in row 2, orange in row 3, pear in row 4. Then, the
next column has a date, such as April 1st in each of these rows. Then , the
next column has the price of the particular fruit on that date.

Now I copy those three rows down and repeat the exercise using the prices of
the 3 fruit on the next day.

I continue doing this until I have 90 rows corresponding to the prices of
the 3 fruits on the 30 days of a month.

Now, I want to be able to automatically lookup the price of a particular
fruit on a particular date. How would I do this with vlookup(s)? If it
would work better, I could first sort things so that I had the 30 day's
prices of apples at the top, then oranges, then pears.

Thanks!

Dean
price of an apple, then orange, then pear, all at the end of March in the
first row

 

[Gary Keramidas]

see if this may help

http://www.contextures.on.ca/xlDataVal02.html


http://www.contextures.on.ca/tiptech.html

 

[Dean]

Thanks but this stuff looks too advanced for what I'm trying to do. It's a
simple problem: find a match of X in column 1, then of Y in column 2, and
return the value in column 3.

One idea I just thought of is to create a column A&B, such as cell C3 =
a3&B3, C4 = A4&b4. Then each column C entry will be unique and I can look
up by that combined variable. But it is a bit tedious that way. Can anyone
suggest a more simple, elegant, way? Almost like a nested conditional
vlookup.

Thanks!
Dean

 

[Bob Phillips]

=INDEX(C1:C100,MATCH(M1&M2,A1:A100&B1:B100,0))

where M1 is your lookup fruit, M2 is your lookup date.