look ups
 

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.

look ups
 

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
---
"chrisgoods" wrote:
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.

look ups
 

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

On Sep 3, 2:23*pm, chrisgoods
wrote:
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.

look ups
 

That worked perfectly. thank you very much

"Max" wrote:

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
---
"chrisgoods" wrote:
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.

look ups
 

Chris, you're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik
---
"chrisgoods" wrote in message
...
That worked perfectly. thank you very much