Forum Discussion
Help with Matching
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.
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.- thank you for the lengthy explanation. none of them worked. still get the #NA. I give up. I will manually go through 2400 lines. This is rediculous.
Which version of Excel has the "Split" function?
Edit: I see you revised your post. Have to be careful with using AI chatbots. Not all solutions given are accurate.
