x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 237

# Excel Look Up Problem

I have a list of orders and a class for list of orders
for simplicity, I have put every in a single worksheet, in real, I have million of such records.

I want to look up the class for each order in Microsoft Excel.
I tried to use VLOOKUP function but it doesn't work.

Hope someone can help!
Thanks!

Orders.xls
0
mawingho
5 Solutions

Commented:
You can only place one value per row in the lookup range.  Have a look at the attached file.
Orders.xls
0

Author Commented:
Of course I know. that's why I want to ask if any method I can perform this without need to manually change the content
0

Commented:
Try this formula in B2 copied down

=LOOKUP(2^15,SEARCH(A2,\$D\$2:\$D\$6),\$E\$2:\$E\$6)

that will give #N/A error if A2 isn't found....or use this version to return text like "No match" in that case

=IF(COUNTIF(\$D\$2:\$D\$6,"*"&A2&"*"),LOOKUP(2^15,SEARCH(A2,\$D\$2:\$D\$6),\$E\$2:\$E\$6),"No match")

regards, barry
0

Commented:
...or perhaps slight revised for added robustness

=LOOKUP("zzz",IF({1,0},"No match",LOOKUP(2^15,SEARCH(" "&A2&","," "&\$D\$2:\$D\$6&","),\$E\$2:\$E\$6)))

This will stop you picking up a "false match", e.g. see the altered entry in A2 on attached example

barry
27373426.xls
0

Commented:
.......now I realise I might be making it a little more complex than it needs to be - you can use a simple "wildcard" here with the lookup value in VLOOKUP, i.e.

=VLOOKUP("*"&A2&"*",D\$2:E\$6,2,0)

That could still suffer from "false matches" if any order number can be contained in another order number e.g. looking up 12002 will find a match with 120023 so to prevent that it's difficult to use VLOOKUP....you can use INDEX/MATCH alternative

=INDEX(E\$2:E\$6,MATCH(" *"&A2&",*",INDEX(" "&D\$2:D\$6&",",0),0))

assumes all order numbers in the lookup range are separated by <comma><space>

regards, barry
0

Commented:
Prevent "#NA" displayed in CLASS column!

Paste the formula below in range B2 and copy it down to range B3:B22

=IF(ISNUMBER(MATCH("*"& A2 &",*",\$D\$2:\$D\$6,0)),INDEX(\$E\$2:\$E\$6,MATCH("*"& A2 &",*",\$D\$2:\$D\$6,0),1),"")

This format will prevent "#NA" when no match is found.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.