Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to look up a value across multiple columns in Excel and Google Sheets
Topic:Lookups (any direction)Excel & Google Sheets
=IFERROR(VLOOKUP(E2,$A$2:$A$5,1,0),IFERROR(VLOOKUP(E2,$B$2:$B$5,1,0),VLOOKUP(E2,$C$2:$C$5,1,0)))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put the value you are searching for in a cell (e.g. E2).
- 2Use nested IFERROR+VLOOKUP formulas to check each column in order.
- 3Each VLOOKUP checks one column; if it returns an error (no match), IFERROR tries the next column.
- 4In Google Sheets, ARRAYFORMULA combined with COUNTIF across a multi-column range is a more elegant alternative.
Tips
- The nested IFERROR approach is simple but scales poorly with many columns. For wide tables, consider using MATCH on a transposed range or restructuring the data.
- Google Sheets FILTER function can search a multi-column range more naturally: =FILTER(A2:C5, (A2:A5=E2)+(B2:B5=E2)+(C2:C5=E2)).
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Can MATCH search across multiple columns at once?
MATCH searches a single row or column. To search multiple columns, use it multiple times or combine with array logic.
Is there a single formula for this in Excel 365?
XLOOKUP can search a multi-column return array, but for searching across multiple key columns, the nested IFERROR approach or restructuring data is typically clearest.
More on Lookups (any direction)
See all →Reference
HLOOKUP
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.
How-toHow to look up the last matching value
Use LOOKUP with a search trick or XLOOKUP with search mode -1 to find the last occurrence of a value in a list.
How-toHow to do a lookup with wildcards
Use VLOOKUP or MATCH with wildcard characters (* and ?) to perform partial-match lookups on text values.
How-toHow to do a case-sensitive lookup
Use INDEX with MATCH and the EXACT function to perform a lookup that distinguishes between uppercase and lowercase text.
How-toHow to look up a value and sum the matches
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.
How-toHow to find the closest matching value
Use INDEX and MATCH with ABS and MIN to find the value in a list that is numerically closest to a target number.