Solved

lookup particular values in a range in Excel

Posted on 2011-03-11
11
323 Views
Last Modified: 2012-05-11
Please see the attached freight.xls.

I need to find the Inbound Freight and Outbound Freight for Item #55002.

In the example,
A36 corresponds with B30:C31.
A56 corresponds with B49:C50.
A79 corresponds with B68:C79.

How do I do this?



 freight.xls
0
Comment
Question by:EEuser5551212
11 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 35112451
how about a55?

This should do it:

=VLOOKUP("Inbound Freight:",OFFSET(INDEX(A:A,MATCH(A36,A:A,0)),-10,1,10,2),2,0)
=VLOOKUP("Outbound Freight:",OFFSET(INDEX(A:A,MATCH(A36,A:A,0)),-10,1,10,2),2,0)

Thomas
0
 

Author Comment

by:EEuser5551212
ID: 35112467
Thanks for the reply.

 I'm very new to Excel - where do I put those formulas?
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35112508
anywhere you want the result to be.

Let me break it down for you.
step 1. MATCH(A36,A:A,0) finds the position of item #55002 in column A
step 2. OFFSET(INDEX(A:A,MATCH(A36,A:A,0)),-10,1,10,2) returns a 2-column range starting 10 rows above where the item # was found, and one column to the right
step 3. VLOOKUP("Inbound Freight:",OFFSET(INDEX(A:A,MATCH(A36,A:A,0)),-10,1,10,2),2,0) looks for the words Inbound Freight: in the leftmost column of that range, and returns the value in the second column of the matched row

Thomas
0
 

Author Comment

by:EEuser5551212
ID: 35112723
Thanks again, but it's not working the way I need.  Can you please see attached freight2.xls.

I pasted the formula in D36 and yes, the item # in A36 has an Inbound freight of C30 and that's what the formula shows.

I then propagated the formula down the column.  Then I looked at D56 and it looks wrong.  It should give the value in C49.

Please note the values in column A are not unique and can appear multiple times.
freight2.xls
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35112755
OK, because of duplicates in the column. If you're going to put the formula there, I propose the following update and simplification:

=IF(LEFT(A36,1)="#",VLOOKUP("Inbound Freight:",$B20:$C34,2,0),"")

starting on cell D36 and copy down.

Thomas
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

 

Author Comment

by:EEuser5551212
ID: 35112904
Still not working.  I pasted in D36 but the formula returns #N/A.  (See freight3.xls.)

Thanks for sticking with this one, I appreciate it.
freight3.xls
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 35113292
Somehow, very weirdly, in the vlookup formula, the space between Inbound and Freight is not a space
Replace that space-looking not-space by a real space and the formula will work.

Thomas
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35113494
I've noticed that before, Thomas. If you copy a formula from an answer on Experts Exchange then spaces become CHAR(160)s.......

barry
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35114015
@EEuser5551212: What version of browser are you using? We're trying to figure out why that formula didn't copy right?

Thanks,

Thomas
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35116658
When copying formulae from the web into Excel always right click and hit 'paste special' and choose 'text.' That tends to prevent such things.
0
 

Author Comment

by:EEuser5551212
ID: 35138384
Thanks very much for all your help Thomas.

Also, some great feedback from Barry and Tommy.

Yes, that "space not a space" was a problem - I put a real space in the formula and it works perfectly.  BTW, the browser I copied from was Chrome.

Thanks again for all the help.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 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

11 Experts available now in Live!

Get 1:1 Help Now