Solved

Excel Look Up Problem

Posted on 2011-09-30
6
225 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
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
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…

776 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