Forum Discussion
Solved
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 ...
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.
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!