VLOOKUP, INDEX MATCH and XLOOKUP explained step by step, with the fixes for #N/A and other lookup errors.
9 articles
HLOOKUP searches across the top row of a table and returns a value from a row below it: =HLOOKUP(lookup_value, table_array, row_index_num, FALSE). Use it for sideways tables, like a rate card with zones across the top. It is VLOOKUP turned on its side. In Excel 2021 or Microsoft 365, XLOOKUP does the same job more safely.
#N/A means "not available": the lookup couldn't find your value. First find the cause: extra spaces (fix with TRIM), numbers stored as text, a typo, or a table range that moved because it wasn't locked with $. If the item is truly missing, show a message with IFNA, or with XLOOKUP's 4th argument.
VLOOKUP can't look to the left: it searches the first column of its table and only returns columns to the right. To return a column on the left, use INDEX MATCH, =INDEX(A2:A9,MATCH(G2,B2:B9,0)), which works in every version, or XLOOKUP, =XLOOKUP(G2,B2:B9,A2:A9), in Excel 2021 or Microsoft 365.
INDEX MATCH is a lookup built from two functions. MATCH finds the position of a value in one column, and INDEX returns the item at that position from another column: =INDEX(A2:A9,MATCH(G2,B2:B9,0)). It can look left, it keeps working when columns are inserted, and it runs in every version of Excel.
A two-way lookup finds the value where a row and a column meet, like units sold for one product in one month. Use INDEX with two MATCHes: =INDEX(B2:E6,MATCH(H1,A2:A6,0),MATCH(H2,B1:E1,0)). The first MATCH finds the row, the second finds the column, and INDEX returns the cell where they cross.
A VLOOKUP approximate match uses TRUE as the 4th argument, like =VLOOKUP(B2,$F$2:$H$5,3,TRUE). It finds the largest value in the first column that is less than or equal to your number, so 62,000 lands in the band that starts at 50,000. The first column must be sorted from smallest to largest.
VLOOKUP finds a value in the first column of a table and returns a value from the same row in another column. Write =VLOOKUP(what to find, table, column number, FALSE). FALSE means exact match, which is what you want for IDs, codes and names. If nothing matches, VLOOKUP returns #N/A.
XLOOKUP finds a value in one range and returns the matching item from another: =XLOOKUP(G2,B2:B9,A2:A9). It defaults to an exact match, can return a column on either side, and has a built-in if_not_found message instead of #N/A. It needs Excel 2021, Excel 2024 or Microsoft 365; it isn't in Excel 2019 or earlier.
XLOOKUP uses wildcards only when its 5th argument, match_mode, is 2: =XLOOKUP("Riverside*",A2:A9,B2:B9,"Not found",2). The * stands for any number of characters and ? for exactly one. Match mode -1 returns an exact match or the next smaller value, and 1 the next larger, which is useful for bands and tiers.