x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 354

lookup particular values in a range in Excel

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
EEuser5551212
1 Solution

Commented:

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 Commented:

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

Commented:
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 Commented:
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

Commented:
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 Commented:
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

Commented:
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

Commented:
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

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

Thanks,

Thomas
0

Commented:
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 Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.