lookup particular values in a range in Excel

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

Thanks for the reply.

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

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

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

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

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

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

barry

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

Thanks,

Thomas

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

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.