google-sheets

In Sheets returing a value from different tables based on multiple criteria

I have a weird issue in that my data is displayed in multiple different tables on on different sheet (6+ actually). This is how its imported from a source.

What I need to do is find the value in multiple different tables that correspond to the selections in 3 dropdowns

In this case I need to match

Then return a pay rate from the applicable table.

I have tried a query with matches and varying combinations of indexing, but cant get it quite right. I would greatly appreciate suggestions.

I created a test page with sample data to show my problem. The actual data is a little denser, but you get the idea from this.

Dropdown 1 - RATE YEAR (ex: 2025 Rates) to select the table being used

Dropdown 2 - POSTION (ex: Cashier) to select the Row of that table to be used

Dropdown 3 - YR of Service (ex: Year 3) to select the column of that table to be used

<Returns Value that matches)

I thought I could do something like

=index(C2:G:10, Match(DROPDOWN1&DROPDOWN2&DROPDOWN3, A1:A&B2:B&C3:C7,0)
Year 5 Year 4 Year 3 Year 2 Year 1
2025 Rates Stocker 12 11 10 9 8
Cashier 13 12 11 10 9
Asst Manager 14 13 12 11 10
Manager 15 14 13 12 11
Year 5 Year 4 Year 3 Year 2 Year 1
2026 Rates Stocked 13 12 11 10 9
Cashier 14 13 12 11 10
Asst Manager 15 14 13 12 11
Manager 16 15 14 13 12

HERE'S THE ACTUAL EXPRESSION I TRIED

I tried to index and match but got a

Error Wrong number of arguments to MATCH. Expected between 2 and 3 arguments, but got 4 arguments.

=INDEX(Sheet2!C7:G22,Match(A6&B6&C6,Sheet2!A7:A&Sheet2!B7:B,Sheet2!C6:G6,0))

Link to Spreadsheet

https://docs.google.com/spreadsheets/d/13Omz4DbdKVsBiIykRS25re_V4A6gp5vO11DRQBjOZKw/edit?usp=sharing

Sample Form

Sample Form

Sample Data

Sample Data

Tried to index(match but cant quite figure out the syntax when you're indexing multiple full tables. Tried a query with ifs but ifs wont return multiple columns.

Solution

  • You may try this formula:

    =IFERROR(INDEX(Sheet2!C7:G22, MATCH(B6, Sheet2!B7:B22, 0)+IF(A6="2026 Rates",6,IF(A6="2027 Rates",12,0)), MATCH(C6, Sheet2!C$6:G$6, 0)), "Rate Year Not Found")

    References: