INDEX MATCH with Multiple Criteria and Cannot Select Same Value Twice

schwang

New Member
Joined
Oct 31, 2018
Messages
3
I have a table that looks like the one below with Work Order # in descending order:

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Employee Work Order #[/TD]
[TD="align: center"]Employee Date Worked[/TD]
[TD="align: center"]Employee Name[/TD]
[TD="align: center"]Employee Total Hours[/TD]
[TD="align: center"]Vehicle Work Order #[/TD]
[TD="align: center"]Vehicle Description[/TD]
[TD="align: center"]Vehicle Date Used[/TD]
[TD="align: center"]Vehicle Total Hours[/TD]
[TD="align: center"]Vehicle Operator[/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]9/26/17[/TD]
[TD]Jason[/TD]
[TD]6[/TD]
[TD]S11421[/TD]
[TD]Pickup Truck[/TD]
[TD]9/26/17[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]9/26/17[/TD]
[TD]Mark[/TD]
[TD]6[/TD]
[TD]S11421[/TD]
[TD]Dump Truck[/TD]
[TD]10/10/17[/TD]
[TD]2.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]10/10/17[/TD]
[TD]Jason[/TD]
[TD]4[/TD]
[TD]S11421[/TD]
[TD]Excavator[/TD]
[TD]10/10/17[/TD]
[TD]2.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]10/10/17[/TD]
[TD]Frank[/TD]
[TD]2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S11421[/TD]
[TD]10/10/17[/TD]
[TD]Austin[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to enter a name in the Operator column. The operator can be any employee as long as he meets these qualifications:
  • Same Work Order #
  • Same Day Worked
  • Worked at least as many hours as the vehicle was used
  • Is not already an operator for another piece of equipment on that same day

I can use an Index Match combination to return a correct value that satisfies the first three equations, but I am not sure how to handle the last qualification of not having repeats. For example, in my formula, both the dump truck and excavator would return Jason as it is the first person who satisfies the first three qualifications.

If possible, I'd prefer to just use formulas to solve this and avoid VBA. Thanks in advance!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top