VLOOKUP Issues

I have two sets of very large data of products I need to find an internal company part number for. I have tried using a program called KuTools to do this and followed these instructions. while it seemed to work, but took 3 days to run (I used this on Office 2016 running on an ARM based MAC). It missed a lot of items and also I want to vet this against doing my own VLOOKUP on Office 2021 for Mac with ARM support. When I tried this the VLOOKUP would not return results... it only showed the formula.

Having said that I may have not set it up correctly. The file I attached is a very small sample of what I am dealing with.


Tab 1: is the data I need to update with a Dealer Part Number. They are not in the same order as the reference data and there are times where some data will be missing (Manufacture Part No. or UPC)

Screen Shot 2022-02-15 at 9.49.39 AM.jpg

Tab 2: is the reference data that I want to look at to pull the dealer part number.

Screen Shot 2022-02-15 at 9.49.48 AM.jpg


How exactly would I look at 2 different columns of matching data (Manufacture Part No. or UPC) using VLOOKUP to populate the Dealer Part No. You can also see that some of the Manufacture Part No.s have special characters in them like slashes, plus signs and quotes... will that affect results? I just need either the Manufacture or the UPC to match in order to validate the Dealer Part number from Tab 2.

I have tried this to just try one column and I couldn't even get that to work - only the formula would show and nothing else. Thank you for guiding me with the sample file I have provided. I need to do this for 350K line items from a reference of over 500K items.

Glenn, thank you kindly for this reply... I paste this formula in C2 and it only shows the formula. Need to figure this out, then I assume I just copy the formula down.

Any ideas on that issue.

I also made sure that Show Formulas is turned off...

I know Valentine's Day was yesterday, but I should say I love you guys (and gals) - thank you for the help.

I will AliGW. My apologies. In this case.. how would you have titled this? VLOOKUP Issues with Merging Tables with Multiple Columns?

I should have known better.

Glenn,

This may be too crazy of a question but is there a way to compare the part numbers to see if they match with or without the special characters?

I have to audit what all they are, but if I use this formula how would it be altered:

=IFERROR(IFERROR(VLOOKUP(A2,eclipse_id,3,FALSE),VLOOKUP(B2,Reference!B:C,2,FALSE)),"")

I know that they will absolutely include the following:

• "
• '
• -
• +
• /
• \
• .
• ,
• #
• (
• )