#n/a in Vlookup

[russelldt]

My problem is as follows.

Have a number in cell B2 (on Sheet 1), and i have an array (Sheet 3, $A$2 to $B$127). The number in Cell B2 may or may not appear in column 1 of the array. If it does, i need the number in column 2 of the array, as the answer to my lookup. The numbers in column 2 of the array are 0 or 1.

The formula only gives me the correct result, when the conditions are met, i.e. there is a corresponding number in column 1 and cell B2, in all other instances the answer is #N/A. I would like a blank or 0 as the answer.

=VLOOKUP(B2,Sheet3!$A$2:$B$127,1,FALSE)

Any help would be appreciated

 

[Joe4]

Just wrap it in an IFNA formula, i.e.

=IFNA(VLOOKUP(B2,Sheet3!$A$2:$B$127,1,FALSE),"")

See: How to use the Excel IFNA function | Exceljet

 

[DanteAmor]

Try:

Libro2
	A	B	C
1
2		88	12345
3
Hoja1

Cell Formulas
Range	Formula
C2	C2	=IFERROR(VLOOKUP(B2,Sheet3!$A$2:$B$127,2,0),0)

 

[Joe4]

IMO, I like the IFNA option a little better here, and that is targeted just to the specific ISNA error.
The IFERROR is a blanket that covers all errors, so if you had made a mistake in entering your formula (so that you get others errors), those would be ignored too (and you may not be aware that you made a typo).

 

[russelldt]

Just wrap it in an IFNA formula, i.e.

=IFNA(VLOOKUP(B2,Sheet3!$A$2:$B$127,1,FALSE),"")

See: How to use the Excel IFNA function | Exceljet

Just wrap it in an IFNA formula, i.e.

=IFNA(VLOOKUP(B2,Sheet3!$A$2:$B$127,1,FALSE),"")

See: How to use the Excel IFNA function | Exceljet

Perfect, thanks for helping and also the link to the IFNA site

 

[Joe4]

You are welcome. Glad we were able to help!