?
Solved

Excel Look Up Problem

Posted on 2011-09-30
6
Medium Priority
?
232 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 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

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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 50

Assisted Solution

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

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

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

649 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