SOLVED

Display Specific Value Based on Multiple Inputs

Copper Contributor

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?

2 Replies
best response confirmed by bmil3015 (Copper Contributor)
Solution

Re: Display Specific Value Based on Multiple Inputs

@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.

Re: Display Specific Value Based on Multiple Inputs

This is perfect, the double XLOOKUP is exactly what I'm looking for. Works on my less generic spreadsheet across multiple tabs. Thank you!

1 best response

Accepted Solutions
best response confirmed by bmil3015 (Copper Contributor)
Solution

Re: Display Specific Value Based on Multiple Inputs

@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.