How to use Multi Criteria in Vlookup using INDEX and Match

In Excel, we use Vlookup for pulling data from a table with single criteria but it can only work with one criteria for matching information. If there are multiple rows in your sheet with the same information, you’ll only get the first one. If you need to use two or more conditions to match a specific piece of data, you’re out of luck.

To Achieve multiple criteria in a vlookup, Excel has a pair of functions called INDEX and MATCH

The syntax for INDEX and MATCH as follow:

=INDEX(array, row_num, [col_num])

The array is the table of data that contains the cell value you want.

The row_num is the relative row number of the cell you want.

The col_num is the relative column number of the cell you want.

=MATCH(lookup_value, lookup_array, [match_type])

The lookup_value is what you are searching for.

The lookup_array is the array of values you are trying to find the lookup_value in.

The optional match_type determines whether MATCH must find the lookup_value exactly (with a 0), or return the closest match that comes before it (with a 1) or after it (with a -1) alphanumerically.

Using INDEX and MATCH with one Criteria

The syntax as follow:

=INDEX(array,MATCH(lookup_value, lookup_array, [match_type]) , [col_num])
=INDEX(C4:H1159,MATCH(A2,C4:C1159,0),6)

Using INDEX and MATCH with Two Criteria

The syntax as follow:

=INDEX(array,MATCH(lookup_value, (lookup_array)*(lookup_array), [match_type]) , [col_num])
=INDEX(C4:H1159,MATCH(1,(C4:C1159=A2)*(D4:D1159=B2),0),6)

When you enter the formula, don’t just press ENTER. Press CTRL+SHIFT+ENTER to tell Excel that it is an array formula. You can tell you’ve done it right because the entered formula will be surrounded in curly braces {}.

Highlight Duplicates in Column using Conditional Formatting

Use Excel conditional formatting to highlight duplicate entries in a specific column, or in a range of cells (multiple rows and columns):

In Excel 2007 or later:

Select the cells to format -- range A2:A11 in this example

On the Ribbon's Home tab, click Conditional Formatting

Click Highlight Cell Rules, then click Duplicate Values

Select one of the formatting options, and click OK

For Excel 2003:

Select the cells to format -- range A2:A11 in this example

Choose Format|Conditional Formatting

From the first dropdown, choose Formula Is

For the formula, enter

=COUNTIF($A$2:$A$11,A2)>1

Click the Format button.

Select a font or fill colour for highlighting.

Click OK, click OK

How the VLOOKUP Function Works

Excel's VLOOKUP function, which stands for vertical lookup, can be used to look up specific information located in a table of data or database.

VLOOKUP normally returns a single field of data as its output. How it does this is:

you provide a name or lookup _value that tells VLOOKUP in which row or record of the data table to look for the desired data.

you supply the column number - known as the col_index_num - of the data you seek.

The function looks for the lookup _value in the first column of the data table.

VLOOKUP then locates and returns the information you seek from another field of the same record using the supplied column number.

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the VLOOKUP function is:

= VLOOKUP ( lookup_value , table_array , col_index_num , range_lookup )

lookup _value (required):
The value you want to find in the first column of the table_array.

table_array - (required):
This is the table of data that VLOOKUP searches to find the information you are after.
The table_array must contain at least two columns of data.
The first column normally contains the lookup_value.

col_index_num - (required):
The column number of the value you want found.
The numbering begins with the search_key column as column 1.
If index is set to a number greater than the number of columns selected in the range argument a #REF! error is returned by the function.

range_lookup - (optional):
It indicates whether or not the range is sorted in ascending order. The data in the first column is used as the sort key.

A Boolean value :
TRUE or FALSE are the only acceptable values.
If omitted, the value is set to TRUE by default.
If set to TRUE or omitted and an exact match for the lookup _value is not found, the nearest match that is smaller in size or value is used as the search_key.
If set to TRUE or omitted and the first column of the range is not sorted in ascending order, an incorrect result might occur. If set to FALSE, VLOOKUP only accepts an exact match for the lookup _value. If there are multiple matching values, the first matching value is returned If set to FALSE, and no matching value for the search_key is found a #N/A error is returned by the function.