Search code examples
excelvlookup

Vlookup that skips #N/A values

In Sheet1!B, I am doing a vlookup in Sheet2!A2:B100 which searches for the value in Sheet1!A and returns the value in Sheet2!B.

E.g., in Sheet1!B2:

=Vlookup(Sheet1!A2,Sheet2!$A$2:$B$100,2,false)

Some of the values in Sheet1!A repeat in Sheet2!A, but some of the corresponding values in Sheet2!B are #N/A, hence some of outputs in Sheet1!B are also #N/A when that #N/A is the first value the formula finds.

How can I adjust the formula to skip those #N/As and return the first non-error response? Open to using index-match or sumproduct.

Solution

  • Without a concrete sample it is difficult to provide an answer, but I guess you can try the following:

    =VLOOKUP(Sheet1!A2,FILTER(Sheet2!$A$2:$B$100, 
 NOT(ISNA(Sheet2!$A$2:$A$100))),2,FALSE)

    or

    =VLOOKUP(Sheet1!A2,FILTER(Sheet2!$A$2:$B$100, 
 1-ISNA(Sheet2!$A$2:$A$100)),2,FALSE)