Help with extracting data more than one row above

[76_shovel]

I have purchase order data provided in the following manor:


Date Num Source Name Item Qty
Jackson, Nick
05/21/2020 2839 Western 87-4601 Rear 1.00
05/21/2020 2839 Western 87-4121 MM90-19 1.00
05/22/2020 2840 Western 87-4110 Bolts 2.00
Total Jackson, Nick
Lepior, Mike
07/29/2019 2713 Dennis Parts 805359 2.00

ect...

My goal is to provide the name of the buyer when entering a item number search even if there is more than one item ordered on same order.

The sample sheet provided works fine for the 1st. entry : example enter 87-4601 and
it returns Jackson, Nick. The problem is entering 87-4121 (same order) returns everything I need except no name because it is more than one row above.


I have been unable to come up with a solution.

[oeldere]

You can solve that with a pivot table.

But since you posted an xls (Excel 2003) file, I can't show it to you.

[Bo_Ry]

Please try

=LOOKUP(MATCH("*"&$B$13&"*",P.O.Sheet!G:G,0),ROW(P.O.Sheet!$B$2:$B$6733)/(P.O.Sheet!$B$2:$B$6733>""),P.O.Sheet!$B$2:$B$6733)

[76_shovel]

Please try

=LOOKUP(MATCH("*"&$B$13&"*",P.O.Sheet!G:G,0),ROW(P.O.Sheet!$B$2:$B$6733)/(P.O.Sheet!$B$2:$B$6733>""),P.O.Sheet!$B$2:$B$6733)

Thank you Bo_Ry !!
a most excellent solution, works great

Thank you
Chuck