Solved

Excel Look Up Problem

Posted on 2011-09-30
6
224 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

910 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

18 Experts available now in Live!

Get 1:1 Help Now