look ups

[chrisgoods]

hi
im looking for a formula that will let me look up two values in one table
array and return one value.
For example: How would i look up a numerical value for product c that is in
the column for 15/01/08

DATE
01/01/08 08/01/08 15/01/08 22/01/08 29/01/08
product a
product b
product c
product d
product e

Ive tried Vlookup with a Hlookup in it and it only searches based on one
criteria. The table will be very large, from colum b to cl and rows 42 to 374.

Any help with this will be greatly appreciated as i have been working on it
for a few weeks and made no headway.

 

[Max]

One way is an index/match ..

Assume table as posted is within A1:F6
B1:F1 are dates
A2:A6 are products

Inputs
In A9: 15/01/08
In B9: product c

Then in C9:
=INDEX(B2:F6,MATCH(B9,A2:A6,0),MATCH(A9,B1:F1,0))
will return the intersection value. Adapt to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik

 

[Pete_UK]

Try this:

=INDEX(B42:CL374,MATCH(A1,B42:B374,0),MATCH(B1,B41:CL41,0))

Put the product you are searching for in A1 and the date in B1.
Assumes your dates are on row 41.

Hope this helps.

Pete

 

[chrisgoods]

That worked perfectly. thank you very much

One way is an index/match ..

Assume table as posted is within A1:F6
B1:F1 are dates
A2:A6 are products

Inputs
In A9: 15/01/08
In B9: product c

Then in C9:
=INDEX(B2:F6,MATCH(B9,A2:A6,0),MATCH(A9,B1:F1,0))
will return the intersection value. Adapt to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik

 

[Max]

Chris, you're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik