Hello readers today's post will be short but I assure you it will help you tackle problem which you may be facing or might face in future. Suppose you want to look up any value based on two criteria, for example in the below image you want to know the price in F8 based on Make & Color in F2 & F5 respectively.
I know the solution to this can be found using filter feature, but being big fan of functions I would like to provide you formula to find it as well. You'll use a combination of INDEX & MATCH function to get your figure here. The formula in F8 will be as follows:
F8: {=INDEX(C2:C10,MATCH(F2&F5,A2:A10&B2:B10,0))}
This is an array formula and to make it works you'll have to press (Ctrl+Shift+Enter) CSE.
Download sample file: MULTI-CRITERIA LOOKUP
No comments:
Post a Comment