Multi Conditional lookup in Excel

Have you ever came across a situation wherein you wanted to return a value which matches both row and column?. Vlookup function in Excel returns the value which is matching the row only. In our previous tutorial, we have shown how to perform two column lookup. In this excel tips post, we will guide you how to perform multi conditional lookup using match and index functions.

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.

Multi Conditional lookup in Excel

To over come above-mentioned limitations of vlookup, we can use match and index function of excel to get a result like conditional vlookup.

Using Match and Index function for conditional lookup 

When Match and index functions of excel 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. To perform conditional lookup, we should understand how match and index functions of excel work.
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.

Conditional LOOKUP 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 an 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 the table as the range: So in Index, we used Table as the 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 returns 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!!!

2 comments… add one
  • [email protected] Jan 8, 2013, 6:23 pm

    please can you help i cannot see how to make a cell have a the same vale as a cell in another file.

    many thanks
    Nicky

  • Kartik Anirudh Jan 8, 2013, 8:31 pm

    Dear Nicky, You need to use vlookup function to check the cell value which are duplicated in the other file.
    For example if your cell data which you need to check is there in Range A1 and the data need to be checked for the same value is in another file name say, Book2 file and data in in range Sheet 1 A1: A10, then enter the vlookup function as mentioned below.
    =VLOOKUP(A1,[Book2]Sheet1!$A$1:$A$10,1,FALSE)
    The cell value which are there in the another file, will return value, which are the value not there, it will return #n/a.

    Hope this helps. Please let me know if you need further help…

Leave a Comment

Shares