Solved

Excel Look Up Problem

Posted on 2011-09-30
6
223 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 300 total points
Comment Utility
...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
Comment Utility
.......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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
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.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now