INDEX MATCH is a powerful formula that combines two Excel functions: the INDEX function and the MATCH function. The XLOOKUP function is easier to use and has some additional advantages.Welcome to the Exciting world of Excel formulas and functions!Īt its core, lies the formidable duo: INDEX MATCH -a groundbreaking combination of functions that redefines data lookups and retrieval. If you have Excel 365 or Excel 2021, use XLOOKUP instead of INDEX and MATCH. To find the closest match to a target value in a data column, use INDEX, MATCH, ABS and MIN in Excel. Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson. Two-column Lookupĭo you want to look up a value based on multiple criteria? Use INDEX and MATCH in Excel to perform a two-column 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. No worries, you can use INDEX and MATCH in Excel to perform a left lookup. The VLOOKUP function only looks to the right.

Note: the formula correctly looks up the salary of MIA Reed, not Mia Clark. However, you can use INDEX, MATCH and EXACT in Excel to perform a case-sensitive lookup. For example, use INDEX and MATCH in Excel to perform a two-way-lookup.īy default, the VLOOKUP function performs a case-insensitive lookup. The INDEX function can also return a specific value in a two-dimensional range. However, you'll need INDEX and MATCH to perform advanced lookups, as we will see next. If you like, you can also use the VLOOKUP function.

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.Įxplanation: the MATCH function returns position 5. The INDEX function below returns a specific value in a one-dimensional range.Įxplanation: the INDEX function returns the 5th value (second argument) in the range E3:E9 (first argument). In this example, we use MATCH to return an exact match so we set the third argument to 0. Explanation: 53 (first argument) found at position 5 in the range B3:B9 (second argument).

Index match