Forum Discussion
Display Specific Value Based on Multiple Inputs
Hello, I'm trying to display a specific value based on multiple inputs. Details:
Rate Schedule Table:
| Description | Rate Schedule #1 | Rate Schedule #2 |
| Personnel A | $70 | $75 |
| Personnel B | $55 | $60 |
| Personnel C | $45 | $50 |
| Personnel D | $30 | $35 |
| Personnel E | $15 | $20 |
This table will be referenced later.
Rate Schedule Selection Table:
| A single cell, uses Data Validation to display a list of the Rate Schedules (Rate Schedule #1, Rate Schedule #2) from the Rate Schedule Table. It defines which Rate Schedule will be followed for the Estimate Table below. |
This table will be referenced later.
Estimate Table:
| Description | Rate |
| This cell uses Data Validation to show a list of the Personnel (Personnel A, Personnel B, ... etc.) from the Rate Schedule Table | I want this cell to display the correct rate for the Personnel selected to the left, based on the Rate Schedule as defined in the Rate Schedule Selection Table. |
| "" | "" |
I tried using XLOOKUP however it seems as though I'm locked to one column and couldn't figure out how to factor in the Rate Schedule Selection Table.
Example of how I'd like it to function:
Rate Schedule Selection Table:
| Rate Schedule #2 (selected from the list) |
Estimate Table:
| Description | Rate |
| Personnel C (selected from the list) | $50 |
Any idea of a function to perform this?
bmil3015 You can try a double-XLOOKUP or INDEX-MATCH-MATCH:
=IFNA(XLOOKUP(selected_personnel, personnel_range, XLOOKUP(selected_schedule, schedule_range, rate_range)), 0) //OR =IFNA(INDEX(rate_range, MATCH(selected_personnel, personnel_range, 0), MATCH(selected_schedule, schedule_range, 0)), 0) //For example: =IFNA(XLOOKUP(F2, A2:A6, XLOOKUP(F1, B1:C1, B2:C6)), 0) //OR =IFNA(INDEX(B2:C6, MATCH(F2, A2:A6, 0), MATCH(F1, B1:C1, 0)), 0)Sample Results
XMATCH can also be used instead of MATCH.
2 Replies
bmil3015 You can try a double-XLOOKUP or INDEX-MATCH-MATCH:
=IFNA(XLOOKUP(selected_personnel, personnel_range, XLOOKUP(selected_schedule, schedule_range, rate_range)), 0) //OR =IFNA(INDEX(rate_range, MATCH(selected_personnel, personnel_range, 0), MATCH(selected_schedule, schedule_range, 0)), 0) //For example: =IFNA(XLOOKUP(F2, A2:A6, XLOOKUP(F1, B1:C1, B2:C6)), 0) //OR =IFNA(INDEX(B2:C6, MATCH(F2, A2:A6, 0), MATCH(F1, B1:C1, 0)), 0)Sample Results
XMATCH can also be used instead of MATCH.
This is perfect, the double XLOOKUP is exactly what I'm looking for. Works on my less generic spreadsheet across multiple tabs. Thank you!
