?
Solved

lookup particular values in a range in Excel

Posted on 2011-03-11
11
Medium Priority
?
340 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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