Solved

lookup particular values in a range in Excel

Posted on 2011-03-11
11
331 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
[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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 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