Solved

# Excel Look Up Problem

Posted on 2011-09-30
Medium Priority
230 Views
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
Question by:mawingho
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 1

Accepted Solution

_Benaiah earned 400 total points
ID: 36890487
You can only place one value per row in the lookup range.  Have a look at the attached file.
Orders.xls
0

Author Comment

ID: 36890494
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

LVL 50

Assisted Solution

barry houdini earned 1200 total points
ID: 36890513
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

LVL 50

Assisted Solution

barry houdini earned 1200 total points
ID: 36890523
...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

LVL 50

Assisted Solution

barry houdini earned 1200 total points
ID: 36890701
.......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

LVL 9

Assisted Solution

experts1 earned 400 total points
ID: 36893604
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month12 days, 23 hours left to enroll