VLOOKUP formula to look at 2 columns

[realniceguy5000]

Hello, This is a cross post question however no solutions from the other site.
http://www.mrexcel.com/forum/excel-q...al-column.html

I currently have the 1st formula below which has been working just fine, however now that we have duplicate lot numbers in the column so now it returns the same count for both lot numbers even though the item numbers are different . What I would like is it to look at an additional column to separate these type of problems. Here is the formula I need to edit:

Please Login or Register  to view this content.

Basically I need to merge the formula above with this one below

Please Login or Register  to view this content.

Could someone help me out?
Thanks as always, Mike

[XOR LX]

Hi,

The difference between these two forums is that you can post attachments here!

I suggest you take advantage of that fact so that we are clear on what your requirements are.

Regards

[realniceguy5000]

Ok no problem sorry for the trouble,

Attached you will find a workbook with 2 worksheets. the first is the order details. the second is called lot scans. The user will scan lot numbers into the lot scan sheet. Lot of VBA goes on after they scan, however what my issues is on the Order Details worksheet with the formula in Column "Q" Which is the first formula from the original post. Since it is looking at Column "E" and finding it on the lot scans it is counting both item numbers from the lot scan worksheet as the same when really they are the same lots but different items.

My thoughts were to have it check both columns "E" on the Order Details and "D" on the order Details and count how many times they were scanned.
OD Sheets "E" are the lots on the Lot scan sheet Column "A" and The item number on the OD Sheet "D" is the item number on the Lot scan sheet Column "B" This is why I thought it was possible to combine the two formulas above.

Let me know if you need more information once you look over the workbook.

Thanks Again, Mike

[XOR LX]

Thanks a lot. Actually, unless I'm missing it, the one thing that would be really useful would be your expected results for that column.

Regards

[realniceguy5000]

I think I am getting a bad reputations with you... My Bad... Sorry you did not miss it... So the count in Order Details column "Q" should be 201 for item 25647 and 2 for item 25648 the others are 0 because no other lots were scanned on the lot scan page. But only if the lot and the item match. Make any better sense?

Thanks for your patients, Mike

[XOR LX]

No worries.

So it seems that you could simply add an extra column into each of these sheets which performs a concatenation of the Item Number and Lot Number for each entry, and then, since this value will be unique, you can now safely use use that as the reference in the LOOKUP.

Or am I missing something?

Regards

[realniceguy5000]

Well that would be a simple approach and I agree except I have so much VBA code working with this workbook that I would have to write / edit and that could take a while. Where as I thought the formula was the best option. I suppose I might be able to write some code before the update takes place to replace those values, I know adding a column would cause a major impact on the logic of the program.

So what do you think, have any other options besides adding a column?

Thank You, Mike

[XOR LX]

Sure. You can perform the concatenation in the formula - it's just quite a bit more resource-heavy:

=IFERROR(INDEX('Lot Scans'!$E$2:$E$1000,MATCH('Order Details'!E2&"|"&'Order Details'!D2,INDEX('Lot Scans'!$A$2:$A$1000&"|"&'Lot Scans'!$B$2:$B$1000,,),0)),"")

Regards

[realniceguy5000]

Thanks for your help and most of all your patience...

Mike

[XOR LX]

Not at all. You're more than welcome.

Cheers