google-sheetsgoogle-sheets-formula

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

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 Form

Sample Data

Sample Data

I've also tried a query with ifs, but ifs won't 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: