Forum Discussion
Help with Matching
I need help. I have a workbook with two tabs. Tab#1 and Tab#2. Tab#1 has column "D" which shows order numbers. Tab#2 has only column "A" but in the row, it shows a description of the order and the order number.
I need to compare the two tabs and find if tab#1 column "D" matches anything exactly in Tab#2. How do I write this? I am so new and have spent 3 hours trying to figure this out. All i get is #NA. Please help.
7 Replies
- You can use the VLOOKUP function to compare the two tabs and find if tab#1 column "D" matches anything exactly in Tab#2. Here's how you can do it:
1. In Tab#2, insert a new column before column A and name it "Order Numbers".
2. In the first cell of the new column, enter the formula `=RIGHT(B2,LEN(B2)-FIND(" ",B2)))`. This will extract the order number from the description.
3. Copy the formula down to all other cells in the new column.
4. In Tab#1, insert a new column after column D and name it "Description".
5. In the first cell of the new column, enter the formula `=VLOOKUP(D2,'Tab#2'!$A$1:$B$1000,2,FALSE)`. This will look up the order number in Tab#2 and return its description.
6. Copy the formula down to all other cells in the new column.
This should give you a list of descriptions that match exactly with order numbers in Tab#1.
The VLOOKUP function is used to search for a value in the first column of a table array and return a value in the same row from another column in the table array.- Thank you for your detailed explanation. I am still getting the #NA. Can it be because the order number, 30822 as an example, is buried in the description that Im trying to find? The description I pulled from a copy of the path in my explorer.
For example, one description is G:\Operations Forms\Delivery Receipts\Atlanta\2023 Delivery Receipts - Atlanta\20230302-UFB Main Unit - C Camera AKS Pkg - 30822
This is in Tab#2. In Tab#1, I have a column where just order number is listed, 30822.
i followed what you explained and still get an #NA in Tab#1 in the new column I created. in Tab#2, when I entered the formula in the new column as you explained, I get a copy of column "B" where it shows the path. it does not extract an order number.I understand your frustration. It can be really frustrating when you're trying to solve a problem and you can't seem to find the answer.
The reason why you're getting #NA is because the order number is not exactly matching the description in Tab#2. The VLOOKUP function is looking for an exact match, so if the order number is buried in the description, it won't be able to find it.
There are a few things you can do to try to solve this problem:
* You can try to extract the order number from the description in Tab#2 using a different formula. For example, you could use the SPLIT function to split the description into a list of words, and then use the FIND function to find the order number in the list.
* You could use the MATCH function instead of the VLOOKUP function. The MATCH function will return the position of a value in a range, so you could use it to find the position of the order number in the description, and then use that position to get the description.
* You could use the INDEX function instead of the VLOOKUP function. The INDEX function will return a value from a range based on its position, so you could use it to get the description of the order number directly.
Here are the formulas you can try:
Using the SPLIT function:
=TEXTSPLIT(B2," ",2)
This formula will split the description in cell B2 into a list of words, and return the second word in the list. The second word in the list will always be the order number.
Using the MATCH function:
=MATCH(D2,'Tab#2'!$A$1:$B$1000,0)
This formula will return the position of the order number in the description in Tab#2. The position will be a number, so you can use it to get the description of the order number by using the INDEX function.
Using the INDEX function:
=INDEX('Tab#2'!$A$1:$B$1000,MATCH(D2,'Tab#2'!$A$1:$B$1000,0),2)
This formula will return the description of the order number in Tab#2. The first argument is the range of cells that contains the description, the second argument is the position of the order number in the range, and the third argument is the column number of the description (which is 2).
I hope this helps.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
HansVogelaar thank you for your help. I cannot share as it has multiple lines of sensitive data and removing all of the sensitive data, i will be left with nothing. I have tried so many different formulas and nothing works. With the amount of videos I have watched, you would think I would find a solution.
