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.