If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|