Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Perform and Interpret Regression Analysis in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Number and Text Filters Examples in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • How to Use Solver Tool in Excel

References

  • How to get last row in mixed data with blanks in Excel
  • Count rows with at least n matching values
  • Extract data with helper column in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • Merge tables with VLOOKUP in Excel

Data Validations

  • Excel Data validation require unique number
  • Prevent invalid data entering in specific cells
  • Excel Data validation allow weekday only
  • Excel Data validation with conditional list
  • Excel Data validation specific characters 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.

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:

{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.

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:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

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

Logical Functions

  • IF, AND, OR and NOT Functions Examples in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples
  • Check multiple cells are equal in Excel
  • IF with wildcards in Excel

Date Time

  • Convert Excel time to decimal seconds
  • How to calculate quarter from date in Excel
  • Extract time from a date and time in Excel
  • How to calculate next scheduled event in Excel
  • Sum race time splits in Excel

Grouping

  • Calculate conditional mode with criteria in Excel
  • Group numbers at uneven intervals in Excel
  • Categorize text with keywords in Excel
  • Running count group by n size in Excel
  • If cell contains one of many things in Excel

General

  • With vs Without Array Formula in Excel
  • How to calculate decrease by percentage in Excel
  • Convert column number to letter in Excel
  • Count cells that do not contain errors in Excel
  • Common Errors in Excel
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning