Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Offset in Excel
  • Two-column Lookup in Excel
  • How to use Excel INDIRECT Function
  • How to use Excel TRANSPOSE Function
  • How to use Excel FORMULATEXT function

Data Analysis Examples

  • Use Data Form to input, edit and delete records in Excel
  • Move chart to a separate worksheet in Excel
  • How to Create Column Chart in Excel
  • Create Scatter Chart in Excel
  • Data Series in Excel

Data Validation Examples

  • Excel Data validation allow uppercase only
  • Excel Data validation number multiple 100
  • Data validation must not exist in list
  • Excel Data validation require unique number
  • Excel Data validation whole percentage only

Exact match lookup with INDEX and MATCH in Excel

by

This tutorial shows how to Exact match lookup with INDEX and MATCH in Excel using the example below;

Formula

{=INDEX(data,MATCH(TRUE,EXACT(val,lookup_col),0),col_num)}

Explanation

Case-sensitive lookup

By default, standard lookups with VLOOKUP or INDEX + MATCH aren’t case-sensitive. Both VLOOKUP and MATCH will simply return the first match, ignoring case.

However, if you need to do a case-sensitive lookup, you can do so with an array formula that uses INDEX, MATCH, and the EXACT function.

In the example, we are using the following formula

{=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3)}

This formula is an array formula and must be entered with Control + Shift + Enter.

Worked Example:   VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

How the formula works

Since MATCH alone isn’t case sensitive, we need a way to get Excel to compare case. The EXACT function is the perfect function for this, but the way we use it is a little unusual, because we need to compare one cell to a range of cells.

Working from the inside out, we have first:

EXACT(F4,B3:B102)

where F4 contains the lookup value, and B3:B102 is a reference to the lookup column (First names). Because we are giving EXACT an array as a second argument, we will get back an array of TRUE false values like this:

Worked Example:   How to create running total in an Excel Table

{FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,etc.}

This is the result of comparing the value in B4 every cell in the lookup column. Wherever we see TRUE, we know we have an exact match that respects case.

Now we need to get the position (i.e. row number) of the TRUE value in this array. For this, we can use MATCH, looking for TRUE and set in exact match mode:

MATCH(TRUE,EXACT(F4,B3:B102),0)

It’s important to note that MATCH will always return the first match if there are duplicates, so if there happens to be another exact match in the column, you’ll only match the first one.

Worked Example:   How to translate letters to numbers in Excel

Now we have a row number. Next, we just need to use INDEX to retrieve the value at the right row and column intersection. The column number in this case is hard-coded as 3, since the named range data includes all columns. The final formula is:

{=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3)}

We have to enter this formula as an array formula because of the array created by EXACT.

Post navigation

Previous Post:

Find missing values in Excel

Next Post:

First match in range with wildcard in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to strip html from text or numbers in Excel
  • DOLLAR function: Description, Usage, Syntax, Examples and Explanation
  • How to extract multiple lines from a cell in Excel
  • Remove text by position in a cell in Excel
  • How to count specific words in a range in Excel
  • Extract time from a date and time in Excel
  • Count times in a specific range in Excel
  • Count birthdays by month in Excel
  • How to enter Today’s Date or Static Date and Time in Excel
  • Get work hours between dates custom schedule in Excel
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment for a loan in Excel
  • SYD function: Description, Usage, Syntax, Examples and Explanation
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • How to set check register balance in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles