VLOOKUP and IFNA give me a 0 when data exists

[i8ur4re]

I have 2 workbooks i want to pull data from and merge into one main page, I have followed the instructions per this Youtube video, but I am getting a 0 in over 50% of the fields that have a valid value.

Video source: https://www.youtube.com/watch?v=yea2Zvb8Q7s

Here is my formula:

=IFNA(VLOOKUP(A213,ASIN1!$A$2:$E$1387,5,FALSE),VLOOKUP(A213,ASIN2!$A$2:$E$1381,5,FALSE))


It works for some, and doesnt for others, not sure what the pattern is at the moment.

I am using Office 365, not sure if i should use IFNA or IFERROR.

Any help would be appreciated.

Thank you

 

[Toadstool]

Hi i8ur4re,

The formula works fine for me as you have it written. The only time I see 0 is if the matching E column value is 0 or it is an empty cell.

[TABLE="width: 200"]
<colgroup><col style="width: 25pxpx"><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: center"]213[/TD]
[TD]XYZ[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD="align: center"]214[/TD]
[TD]ABC
[/TD]
[TD]Hello[/TD]
[/TR]
[TR]
[TD="align: center"]215[/TD]
[TD]PUP[/TD]
[TD="align: right"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: center"]216[/TD]
[TD]DOG
[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]217[/TD]
[TD]TTT[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

i8ur4re​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B213
to
B217
[/TH]
[TD="align: left"]=IFNA(VLOOKUP(A213,ASIN1!$A$2:$E$1387,5,FALSE),VLOOKUP(A213,ASIN2!$A$2:$E$1381,5,FALSE))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

	A	B	C	D	E
Code	Number
XXY
PPP
ABC	Hello

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

ASIN1

	A	B	C	D	E
Code	Number
FFD
DOG
XXS
TTT
XYZ

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11
[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]33[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]88[/TD]

</tbody>

ASIN2