Lookup returning #n/a – would like cell to be blank

vlookup

I am using this formula to look across multiple sheets and return the value:

=VLOOKUP(A2,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!a2:a100"),A2)>0,0))&"'!a2:e100"),3,0)

where there is no data to return #N/A is returned, How can I have that cell remain blank instead?

Best Answer

Excel has a function IFNA() for exactly this purpose.

=IFNA(VLOOKUP(A2,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!a2:a100"),A2)>0,0))&"'!a2:e100"),3,0),"")

The formula above wraps yours into an IFNA with the result if NA being blank. (note the addition at the end.) You could also have it return something else, like "invalid" or "none" or even the results of a different formula.

Related Question