I have my data displayed on multiple tables in 6+ different sheets. This is how it's imported from a source.
What I need to do is find the value in different tables that correspond to the selections in 3 dropdowns.
In this case I need to match the following:
Then return a pay rate from the applicable table.
I have tried a query with matches and varying combinations of indexing, but I can't get it quite right.
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:
Dropdown 2:
Dropdown 3:
The result should return the 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)
The actual expression I tried:
=INDEX(Sheet2!C7:G22,Match(A6&B6&C6,Sheet2!A7:A&Sheet2!B7:B,Sheet2!C6:G6,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 | Stocker | 13 | 12 | 11 | 10 | 9 |
Cashier | 14 | 13 | 12 | 11 | 10 | |
Asst Manager | 15 | 14 | 13 | 12 | 11 | |
Manager | 16 | 15 | 14 | 13 | 12 |
I tried to index and match but got the error: Wrong number of arguments to MATCH. Expected between 2 and 3 arguments, but got 4 arguments.
Link to Spreadsheet
https://docs.google.com/spreadsheets/d/13Omz4DbdKVsBiIykRS25re_V4A6gp5vO11DRQBjOZKw/edit?usp=sharing
Sample Form
Sample Data
I've also tried a query with ifs, but ifs won't return multiple columns.
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: