Vlookup based on lookup value and multi number criteria

[randrze2]

I have been stuck with this problem for a while. What I am trying to do is return a reason based on an Order. How it works is that an Order is placed and may have multiple line items which would have a total number of units but when the Order is actually selected, it is done so randomly but shows which Order number was chosen. For example, in Sheet_2, Order X has 2 line items so the total number of units ordered is 9. The first 3 have a reason of "Fix" and the next 6 have a reason of "Clean". Then in Sheet_1, I am trying to return the Reason from Sheet_2 but the problem I am having is that Sheet_1 contains the specific selection number which I cannot figure out a way to work around. Any help is greatly appreciated. Thanks!





Sheet_1: table with lookup value (item) and want to return value to
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]Selection Date[/TD]
[TD]Selection #[/TD]
[TD]Returned Reason[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]9/5[/TD]
[TD]1[/TD]
[TD]Fix[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]9/7[/TD]
[TD]5[/TD]
[TD]Clean[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]9/5[/TD]
[TD]3[/TD]
[TD]Fix[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]9/7[/TD]
[TD]8[/TD]
[TD]Clean[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]9/10[/TD]
[TD]2[/TD]
[TD]Clean[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]9/11[/TD]
[TD]5[/TD]
[TD]Upgrade[/TD]
[/TR]
</tbody>[/TABLE]



Sheet_2: table for lookup array
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]Request Date[/TD]
[TD]Total # Requested[/TD]
[TD]Reason[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]9/4[/TD]
[TD]3[/TD]
[TD]Fix[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]9/5[/TD]
[TD]6[/TD]
[TD]Clean[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]9/7[/TD]
[TD]3[/TD]
[TD]Clean[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]9/8[/TD]
[TD]2[/TD]
[TD]Upgrade[/TD]
[/TR]
</tbody>[/TABLE]