Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

lookup particular values in a range in Excel

Posted on 2011-03-11
11
Medium Priority
?
343 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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.

597 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