Vlookup Finds First Number,Needs to Continue On the list

**NBVC** · 08-28-2011, 02:37 PM

So even if the item was not ordered, you want the item number to come up?

And if so, if there is a duplicate invoice for the same UPC, it will show any one of the two, that't ok?

**NBVC** · 08-28-2011, 03:44 PM

If the answer was yes, then replace the "Not Ordered" part of the formula with a Vlookup...

e.g.

=VLOOKUP(A2,'UPC Verification'!$A$2:$B$99,2,FALSE)

**snb** · 08-28-2011, 04:32 PM

Unless I misunderstood I thought this would do in F6 in sheet 'order':

PHP Code:


=IF(Countif('UPC Verification'!$B:$B,A6)=0,0,VLOOKUP(A6,'UPC Verification'!$B:$C,2,0))

**realniceguy5000** · 08-29-2011, 06:57 AM

Originally Posted by NBVC

So even if the item was not ordered, you want the item number to come up?

And if so, if there is a duplicate invoice for the same UPC, it will show any one of the two, that't ok?

Thanks for pointing that other issue out, you are right.It's possible the upc they enter will not return the correct item number. Much like we started the whole process with. So the data wouldn't be any good. I think but not sure, I should leave the script alone with your change that points out the items that are not on the order.

I think at the end of the order I will have to use some VBA to loop though our range to check for "Not ordered" if found I will have the Auditor enter the item number of the product that was not found on the order.

I will run some tests today to verify how the new formula works in the test box.

I will let you know how the test go,

Thanks so much for all the follow ups to this thread.

Mike

**NBVC** · 08-29-2011, 10:10 AM

I think this formula, a bit shorter, and hopefully more efficient might work too...

=IF(A2="","",IF(ISNUMBER(MATCH(A2,'UPC Verification'!$A$2:$A$99,0)),IF(SUMPRODUCT(COUNTIF(Order!$A$6:$A$30,'UPC Verification'!$B$2:$B$99))=0,"Not Ordered",INDEX('UPC Verification'!$B$2:$B$99,MIN(IF(ISNUMBER(MATCH('UPC Verification'!$B$2:$B$99&'UPC Verification'!$A$2:$A$99,Order!$A$6:$A$30&A2,0)),ROW('UPC Verification'!$A$2:$A$99)-ROW('UPC Verification'!$A$2)+1)))),""))

**realniceguy5000** · 08-29-2011, 10:51 AM

Hi, No this one isn't working.

However,

I just thought of something... Since the order is pasted into the order sheet, and all the worksheets numbers are formated as text I wonder if this is why we are having problems? I see in the formula were looking for isnumber and since they are formatted as text is it possible that this is why we are having issues? Just a shot in the dark...

If I format the UPC as a number it drops the zero that are leading. I think that is why I went with this format.

But I have made so many changes now I cant remember, Anyway as the workbook sits now your other formula works you posted , Just Long... I have another post trying to get that into the cell with VBA.

However this one above again returns the wrong item number which seems to be the first item on the UPC Sheet.

This has turned into a nightmare. But many thanks for sticking it out with me.

Mike...

**NBVC** · 08-29-2011, 11:11 AM

The ISNUMBER() is not checking if the input is numeric, it is checking the result of the MATCH() function to see if it is numeric, meaning the match was found and a position was returned.

In what way didn't the above formula work? Did you confirm with CSE keys? With my testing it seemed to work unless I missed a case

**realniceguy5000** · 08-29-2011, 11:56 AM

Hi,

Oh ... Well so much for my thinking,

anyway
with the test wb, I added 3 item numbers from the upc sheet to the order sheet .
15608
15776
16780

When I scanned the upc for 15776 10786560157763
it returns item 15606

so it appears that say you add the formula to 12 cells on the scan sheet if you add this upc above you will see it appears to just go down the list of item numbers from the upc verifcation page.

So not sure about that formula.

Thank You, Mike