Most of the time, we need to look up values based on multiple column criteria.
However, the standard VLOOKUP function allows searching based on a single column with a single criterion. In this article, we will explore how to perform lookups with multiple column criteria using the powerful combination of the INDEX and MATCH functions.
To Lookup with Multiple Column Criteria, we will use following Excel Formula.
Formula = INDEX(Output_Range,Match(1,(Lookup_column1=Lookup_criteria1)*(Lookup_column2=Lookup_criteria2),0))
Lets Consider a Sample table Below :
In the Bove table we want to Search Orange Colored cell and Want output from column 4 (Revenue) of Rs. 1,002 Crore.
Now Here we want to create formula that can search All first three columns (Region, Sales Person and Product) and if Orange colored Cell Matches at same row then it should get Output from Column 4 (Revenue).
Let’s See how it works.
In the Cell (I7) we have entered the formula. which is shown in formula bar as well as in Cell (I5).
Variables in the Formula,
Output_range = Column in which required data exist like D3:D18.
Lookup_Column = In which column we want to search, like A3:A18.
Lookup_Criteria = Data to be search in Lookup_column, like F4.
Here, You can search any number of columns your required without any Limit.
Note:
- If you are using Excel version other than 2019 or 365, Ensure that you press
Ctrl + Shift + Enterafter typing the formula to create an array formula. - If you are using Excel 365 or Excel 2019, you can use the new dynamic array formulas which don’t require
Ctrl + Shift + Enter.
