Solved

Excel Look Up Problem

Posted on 2011-09-30
6
229 Views
Last Modified: 2012-06-27
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
Comment
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
  • Learn & ask questions
6 Comments
 
LVL 1

Accepted Solution

by:
_Benaiah earned 100 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

by:mawingho
ID: 36890494
thanks for your reply.
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

by:barry houdini
barry houdini earned 300 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 300 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

by:barry houdini
barry houdini earned 300 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

by:experts1
experts1 earned 100 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question