How to get vlookup to work on every line?

**mrsewer** · 09-19-2013, 05:41 PM

Hi,

I was trying to reconcile a 3000-rows report using vlookup. Problem is every row that has unmatch PO# the vlookup will shows as NA which is fine but then the row after that got screwed up. So I have to go after every NA rows and fix the formula. It's a PITA! Please see below for example.

My question is, how do I get vlookup to correctly work on every row? Or is there any other function I can use? Help please!

Thanks so much!!

Column A Column B Column C Column D

01003PO46 $189.98
01003PO59 $10.41 01003PO59 10.41 -->Vlookup got screwed up on this row because it shows NA on the previous row.
01004PO42 $180.00
01004PO55 $40.00 01004PO55 39.98 -->Same problem here
01005PO34 $70.95
01006PO32 $140.00
01006PO44 $40.00 01006PO44 39.98
01008PO45 $274.95
01008PO61 $63.91 01008PO61 63.62
01009PO34 $140.00
01010PO37 $100.00
01011PO65 $334.95
01015PO66 $320.00
01015PO82 $54.91 01015PO82 54.8
001016PO43 $82.50 01019PO57 39.98
01018PO44 $117.13 01020PO52 10.95
01019PO43 $154.95 02001PO59 39.98
01019PO57 $40.00 02003PO62 45.81
01020PO37 $163.92 02004PO61 59.97
01020PO52 $10.95 02006PO57 70.38
01021PO36 $100.82 02009PO62 20.82
01022PO53 $291.90 02012PO74 59.97
02001PO43 $120.82 02013PO58 240.77
02001PO59 $40.00 02014PO87 8.79
02002PO40 $133.62 02015PO60 21.36

**FDibbins** · 09-19-2013, 05:45 PM

Sorry, but without seeing the rest of what you are working with (and what your formula looks like), its hard to offer suggestions

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that

**mrsewer** · 09-19-2013, 06:17 PM

Here you go, please see the attached. Sorry I never attached any file on here before. Not sure if you can see it. If you could, please look at row 5 for example. I use vlookup in column C to get the amount from column F, refer to PO number from column E) and as you can see vlookup worked on row 3 but on row 4 it shows NA because it couldn't find any data from column E and F, which I understand that.

But then on row 5, why the function all the sudden didn't work? You can see it has data in colomn E&F (yellow highlight) and so every row after row 4 shows NA.

Thanks a lot!!!