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

Data Analysis

  • How to count table columns in Excel
  • Example of COUNTIFS with variable table column in Excel
  • Conditional Formatting Data bars Examples in Excel
  • How To Compare Two Lists in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel

References

  • Offset in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel VLOOKUP Function
  • How to get address of last cell in range in Excel
  • Perform case-sensitive Lookup in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation must not contain
  • Excel Data validation exists in list
  • Excel Data validation allow uppercase only
  • Excel Data validation whole percentage only

Excel Advanced Lookup using Index and Match Functions

by

Instead of using VLOOKUP, Use INDEX and MATCH and become an Excel pro. . To perform advanced lookups, you’ll need INDEX and MATCH.

Match

The MATCH function returns the position of a value in a given range. For example, the MATCH function below looks up the value 53 in the range B3:B9.

Explanation: 53 (first argument) found at position 5 in the range B3:B9 (second argument). In this example, we use the MATCH function to return an exact match so we set the third argument to 0.

Index

The INDEX function below returns a specific value in a one-dimensional range.

Explanation: the INDEX function returns the 5th value (second argument) in the range E3:E9 (first argument).

Worked Example:   How to retrieve first match between two ranges in Excel

Index and Match

Replace the value 5 in the INDEX function (see previous example) with the MATCH function (see first example) to lookup the salary of ID 53.

Explanation: the MATCH function returns position 5. The INDEX function needs position 5. It’s a perfect combination. If you like, you can also use the VLOOKUP function. It’s up to you. However, you’ll need INDEX and MATCH to perform advanced lookups, as we will see next.

Two-way Lookup

The INDEX function can also return a specific value in a two-dimensional range. For example, use the INDEX and the MATCH function in Excel to perform a two-way-lookup.

Worked Example:   Get column name from index in Excel Table

Case-sensitive Lookup

By default, the VLOOKUP function performs a case-insensitive lookup. However, you can use the INDEX, MATCH and the EXACT function in Excel to perform a case-sensitive lookup.

Note: the formula correctly looks up the salary of MIA Reed, not Mia Clark.

Left Lookup

The VLOOKUP function only looks to the right. No worries, you can use the INDEX and the MATCH function in Excel to perform a left lookup.

Note: when we drag this formula down, the absolute references ($E$4:$E$7 and $G$4:$G$7) stay the same, while the relative reference (A2) changes to A3, A4, A5, etc.

Worked Example:   Exact match lookup with INDEX and MATCH in Excel

Two-column Lookup

Do you want to look up a value based on multiple criteria? Use the INDEX and the MATCH function in Excel to perform a two-column lookup.

Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson.

Closest Match

To find the closest match to a target value in a data column, use the INDEX, MATCH, ABS and the MIN function in Excel.

Post navigation

Previous Post:

Excel Two-Way Lookup Example

Next Post:

Function Keys f1 – f12 in excel — Examples

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

  • How to use Excel OR Function
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • How to use IFS function in Excel

Date Time

  • Next biweekly payday from date in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • EOMONTH function: Description, Usage, Syntax, Examples and Explanation
  • HOUR function: Description, Usage, Syntax, Examples and Explanation
  • TIMEVALUE function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group times into unequal buckets in Excel
  • Running count group by n size in Excel
  • How to randomly assign people to groups in Excel
  • Group times into 3 hour buckets in Excel
  • How to randomly assign data to groups in Excel

General

  • Creating and Opening an existing file in Excel
  • AutoRecover file that was never saved in Excel
  • How to fill cell ranges with random number from fixed set of options in Excel
  • How to create dynamic named range with INDEX in Excel
  • How to calculate total from percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning