CODEWITHSUNDEEP
excelexcel-formula
Michael Maxwell
Michael Maxwell

Reputation: 1

VLOOKUP will return #N/A when there is a period in the cell value

I am writing a formula for excel to find a value when the cell A13 is changed then compare the value of A13 to the other worksheets in the workbook and then return the value it found. I am struggling to get this formula to work because for some reason when the cell has a period in it VLOOKUP returns "#N/A". It sometimes works when I use TRUE and do an approximate match. Here is an Example of a value that returns #N/A when searching for an exact match: ABCD.8G74. I would like to use FALSE in vlookup to get an exact match. Here is the formula:

=IF(VLOOKUP(A13,sheet2!G2:J5,1,FALSE)=A13,VLOOKUP(A13,sheet2!G2:J5,2,FALSE),IF(VLOOKUP(A13,sheet3!G2:J5,1,FALSE)=A13,VLOOKUP(A13,sheet3!G2:J5,2,FALSE),IF(VLOOKUP(A13,sheet4!G2:J5,1,FALSE)=A13,VLOOKUP(A13,sheet4!G2:J5,2,FALSE),"0")))

if you have any more questions let me know. Thanks for yalls help!

Upvotes: 0

Views: 1326

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

The problem is when it does not find it on the first sheet it returns #N/A and trying to compare an error to a cell value will return the Error and short circuit the IF.

Wrap your VLOOKUPS in IFERROR:

=IFERROR(VLOOKUP(A13,sheet2!G2:J5,2,FALSE),IFERROR(VLOOKUP(A13,sheet3!G2:J5,2,FALSE),IFERROR(VLOOKUP(A13,sheet4!G2:J5,2,FALSE),0)))

Upvotes: 0

Nitesh Halai
Nitesh Halai

Reputation: 927

Exact match by definition means that it will look for the EXACT value in the lookup table. If that value doesn't exact in the table, it returns #N/A. If the value you are entering in A13 has periods, while the value in table doesn't have periods, then you need to find a way to remove the periods before performing the vlookup.

You can use the substitute function to do this and pass that as the lookup value:

=SUBSTITUTE(A13,".","")

Upvotes: 1

Related Questions