Have an interesting situation where if 2 or more workers are scheduled, then the manager with the higher budget covers the cost. Rows 8 to 12 show the desired solution in columns B & C. Column G shows the order of budget size with 1 being the biggest budget, hence when 2 or more workers are scheduled the manager with the highest budget will incur the cost. Am looking for the easiest way to solve this whether it's Power Query, formulae or VBA (providing it's not too complex).
| Lookups Match.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | |||||||||
| 2 | Descriptor | Personnel | Manager | Budget | |||||
| 3 | Sam worked 5 hours | Sam | Tim | 2 | |||||
| 4 | Mary 6 hours Tom 2 hours | Mary | Jack | 1 | |||||
| 5 | Peter all day | Peter | Ken | 3 | |||||
| 6 | Tom | Fred | 4 | ||||||
| 7 | |||||||||
| 8 | Descriptor | Worked | Manager charged | Personnel | Manager | ||||
| 9 | Sam worked 5 hours | Sam | Tim | Sam | Tim | ||||
| 10 | Mary 6 hours Tom 2 hours | Mary, Tom | Jack | Mary | Jack | ||||
| 11 | Peter all day Sam 3 hours | Peter | Tim | Peter | Ken | ||||
| 12 | Tom | Fred | |||||||
| 13 | |||||||||
| 14 | |||||||||
Sheet12 | |||||||||
