Thread: Vlookup Finds First Number,Needs to Continue On the list

Good Morning,

I have an issue that came up this morning during testing.

Here is the setup:

I have Auditors copy an order number to the orders worksheet. Then they would goto the UPC Scan sheets to start scanning UPC into col A. I have a Vlookup formula in col B that looks up the UPC that was scanned in Col A However it appears that our company has some items that have the same UPC for two items.

I have included a workbook to help with the issue. In the example in the workbook I have only one item listed Item 16781 on the order page. However when I scan the UPC on the UPC scan page it returns item 16780. So it reports an error,

I guess I need a way to use vlookup to look up the UPC that was scanned and if the item returned is not a match to what the item is on the order sheet then find the next item linked to that UPC?

It sounds a bit confusing but hopefully you can see what I mean when you look at the workbook.

Thanks for any help with the project.

Mike

Is there any way we can add a "hidden helper column" to the Order sheet, that looks for the matching UPC in the UPC Verification sheet for the Item entered, then when you scan in the UPC SCANS sheet, it uses the Order sheet to lookup the item? Otherwise, not an easy task, especially if both items that could match were entered into the order.

Okay maybe we can try this first without the helper column, since you say Sales assures you multiple items that can match the UPC won't occur.

Try, in B2 of UPC SCANS sheet:

=IF(ISNUMBER(MATCH(A2,'UPC Verification'!$A$2:$A$99,0)),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))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER.

Note: adjust ranges to suit, or make dynamic range(s). I have allowed for upto 30 order lines and up to row 99 of the UPC verifications.

I am not a VBA guy... but I think it's something like:

Sheets(1).Range("B2:B100").FormulaArray = "=IF(ISNUMBER(MATCH(A2,'UPC Verification'!$A$2:$A$99,0)),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))),"""")"

but when I test in immediate window it gives runtime error 1004: unable to set the formulaArray property of the Range class and I don't know why because it works using .Formula

Ok Same thing happened to me, Let me post this question for the guys over in the vba department.

However Thanks again so much for your help.

One last question...(yeah right your saying about now)

Do you think this formula is going to slow the worksheet up? The reason I ask is because the previous formula was not noticeable. And the real UPC list can be over 8000 rows long. I'm trying to make it as fast as possible with out lag time between scans.

Anyway Thank You, Mike

With 8000 UPC it shouldn't be too bad, especially if your Order list is small too... but using the helper column I mentioned would significantly increase efficiency and speed.

Ok, I played around a little with the helper column but wasnt very succesful. I kept getting an NA Error.

This is a growing project so after I get this up and running correctly I may revisit the idea.Next year we are changing our order process so people will be able to order less than case qty. meaning I will have to add all the inner and each UPC into the mix.


Thanks, Mike

Thanks for posting the link Mike.

From PM

Hi,

If you get time, The formula you gave me in this post. Has one issue I was hoping you could see what might be wrong.

http://www.excelforum.com/excel-work...ml#post2587281

Post # 4

if you open the workbook I provided you should see the problem I am having if you do the following:
1st copy your formula to upc scans B2 and drag down to B9 or so.

2nd Enter this item number (5) in A6 of the orders page.

3rd goto the upc verifcation sheet and copy any item numbers upc and paste the value into the upc scan page.

You should see in B2 of the UPC Scan sheet item number 15606 show up but it is the wrong Item for that UPC? I also tried to type the number in and the same thing happend.

Anyway it appears that any upc you type in comes up with item 15606

Do you know how to fix?

Try:

=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)),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))),""),"")

Before we go to the helper route, try this one... it's turning into a mega formula, but I think it works.

=IF(A2="","",IF(ISNUMBER(MATCH(A2,'UPC Verification'!$A$2:$A$99,0)),IF(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))=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))))))