Multi Conditional Vlookup in Excel

Vlookup is a very useful function in Excel. Vlookup looks for a value and provides the corresponding value or the given lookup value. There some limitations of vlookup. This method of lookup will return the value which is there in the intersection of matching row and column. For two column lookup you can read more here. It is not a multi conditional vlookup but, it is a work around way to get the result of vlookup in excel when there is multiple criterias.

Limitations of Vlookup:

  • Vlookup can be used only if the lookup value is in left of the data which we need to extract from the Table or data.
  • Vlookup works with with one criteria. That is, lookup value is maximum one.

So there is a way to overcome from these limitations. These limitations can be overcome by using Match and Index functions.
Use of Match and Index function for lookup:  When Match and index functions used together, we can extract the data from a table irrespective of the weather lookup value is left side or right side of the array. So first let us understand Match and Index functions.
Excel Index Function: Excel Index function returns a value or reference from a table or range.
Syntax of Excel Index Functions:
=INDEX(array, row_num, [column_num])
Explanation of Index Function components:
Array: Is a range or table where we need to extract the data.
row_num: In which row the required value is there.
[column_num]: In which column the lookup value is present.

VLOOKUP in Excel Examples

Table1:

A
B
C
1
Name
Marks
Class
2
Arjun
20
3
3
Beema
30
4
4
Nakula
40
5
5
Sahadeva
50
6

=INDEX(A1:C5,2,3)  returns 3. We are looking for a data which is 2nd row and 3rd column.
Excel Match Functions: Match function returns relative position of the specified item is a range of cells.
Match Function Syntax:
MATCH(lookup_value, lookup_array, [match_type])
1. Lookup_value: The value you need to lookup.
2.Lookup_Array:The range where you need to search
3.[match_type]: Match_type can be -1, 0, or 1. It tells Excel how to match the lookup_value to values in the lookup_array.
1 — find the largest value less than or equal to lookup_value
       (the list must be in ascending order)
 0 — find the first value exactly equal to lookup_value. Lookup_array
         (the list can be in any order)
-1 — find the smallest value greater than or equal to lookup_value.
         (the list must be in descending order)
Note: If match type is omitted, by default excel consider it as 1.

Example for Excel Match Function:  Consider the same table as above.
=MATCH(“Sahadeva”,A1:A5,0) returns 5. That is the value “Sahadeva” is in 5th row.

Using Index and Match together as alternative to vlookup: Using index match together will help us in finding 2 criteria lookup and values are present in left of the lookup value.

Syntax:
=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), MATCH(lookup_value, lookup_array, [match_type]))

Here what we did is instead of finding row and column numbers we used Match function to find.
Example:
=INDEX(A1:C5,MATCH(“Sahadeva”,A1:A5,0),MATCH(“Class”,A1:C1,0)) This returns 6.

Explanation:
We are looking into table as range: So in Index we used Table as range.Using Match function we found rows number of our lookup value “Sahadeva”. For column number we once again used Match function to find another criteria column hnumber. That is we are searching for Sahadeva’s Class. So Class is in 3 row. So the function return the value which is in 5th row and 3rd Column.

Meet the Author

Hi, I am Aniruddha, an avid blogger since 2008 who likes writing about new gadgets, smartphones and excel. You can find them at www.tech2touch.com. You can also follow me on Twitter @tech2touch or my Facebook page tech2touch. You can subscribe to my YouTube channel tech2touch. Enjoy reading and watching!!!

Shares