Multiple lookup in a single formula ..
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. |
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 "sansk_23" wrote in message ... 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. |
=INDEX(B2:M10,MATCH("Prod c",A2:A10,0),MATCH("Apr",B1:M1,0))
-- HTH Bob Phillips "sansk_23" wrote in message ... 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. |
All times are GMT +1. The time now is 11:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com