Multiple lookup in a single formula ..

[Guest]

Can we use hlookup & vlookup in a single formula ?
If i have the data on the Product monthly wise sales in the form of an array.
jan / feb / mar / apr / may..........
Prod a
Prod b
Prod c
Prod e
.......and so on.
How do i retrieve the value of Product c in the month of Apr ?
This would mean we would require to use hlookup as well as vlookup formulae.

Pls. help.

br, Sk.

 

[Niek Otten]

With the date you look for in a20 and the product in A21:

=VLOOKUP(A21,A2:M15,MONTH(A20),FALSE)

So don't include the month headers in the area in the formula and do include
the FALSE argument, otherwise it will return the wrong result if the product
can not be found, instead of an error value.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Bob Phillips]

=INDEX(B2:M10,MATCH("Prod c",A2:A10,0),MATCH("Apr",B1:M1,0))