VLOOKUP and IFNA give me a 0 when data exists

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
97
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
 
Last edited:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.

AB
213XYZ88
214ABC
Hello
215PUP#N/A
216DOG
0
217TTT0

<colgroup><col style="width: 25pxpx"><col><col></colgroup><tbody>
</tbody>
i8ur4re

Worksheet Formulas
CellFormula
B213
to
B217
=IFNA(VLOOKUP(A213,ASIN1!$A$2:$E$1387,5,FALSE),VLOOKUP(A213,ASIN2!$A$2:$E$1381,5,FALSE))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>




ABCDE
1CodeNumber
2XXY1
3PPP2
4ABCHello

<tbody>
</tbody>
ASIN1




ABCDE
1CodeNumber
2FFD11
3DOG
4XXS33
5TTT0
6XYZ88

<tbody>
</tbody>
ASIN2
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top