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

see if this may help

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


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


--


Gary


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

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

"Gary Keramidas" wrote in message
...
see if this may help

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


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


--


Gary


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

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

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

--
HTH

Bob Phillips

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