Lookups (any direction)

Every lookup variant beyond VLOOKUP / XLOOKUP / INDEX-MATCH — HLOOKUP, LOOKUP, two-way, case-sensitive, nth-match. The full lookup toolkit.

13 pages · 1 reference, 12 how-to

Reference

Look up a value in the top row of a table and return a value from a row below it — the horizontal cousin of VLOOKUP.

Use LOOKUP with a search trick or XLOOKUP with search mode -1 to find the last occurrence of a value in a list.

Use VLOOKUP or MATCH with wildcard characters (* and ?) to perform partial-match lookups on text values.

Use INDEX with MATCH and the EXACT function to perform a lookup that distinguishes between uppercase and lowercase text.

Use SUMIF or SUMIFS to sum all values that match a lookup criterion, returning the total of all matching rows rather than a single result.

Use INDEX and MATCH with ABS and MIN to find the value in a list that is numerically closest to a target number.

Use MATCH with a combined range or INDEX to search for a value across several columns and return the column it was found in or its adjacent value.

Use FILTER (Sheets/Excel 365) or an INDEX/SMALL/IF array formula to return all matches for a lookup value.

Use INDEX with MATCH to return multiple columns from a matched row, or use XLOOKUP in Excel 365 and Sheets to spill an entire row of results.

Use MAXIFS (Excel) or MAX with IF as an array formula to find the latest date associated with a specific item in a list.

Use TEXTJOIN with IF as an array formula in Excel, or TEXTJOIN with FILTER in Google Sheets, to find all matching values and join them into one cell.

Use MATCH with a horizontal range to locate the column position of the first value meeting your criteria, then pair with INDEX to retrieve it.

Use SMALL with IF as an array formula to retrieve the position of the nth match, then INDEX to return the corresponding value from any column.