Looking for help on formula while pulling data from a different GSheet using Index & Match?

Question

Facing a #NA error while pulling data from a different google sheet, using Index & Match. Need Test1 (GSheet) data pulled in to Test2 (GSheet) on yellow highlighted cells.

Test1 - https://docs.google.com/spreadsheets/d/1PMcSOsHoqlz5-CWiZHoTGHN7SQ0p3dSzMJAVuYPVG1E/edit?gid=0#gid=0

Test2 - https://docs.google.com/spreadsheets/d/1yL38Y011zeAvD12S9XwxOebGAAf9lqx9PqDSX0G4C3M/edit?gid=0#gid=0

Please help with the formula from the B1 cell.

Thanks.

Answer 1

IMPORTRANGE is a pretty expensive, so you should probably just make this query in the source spreadsheet. But if that is not possible, then here is my proposed solution:

=LET(salesman, "AAA",
    QUERY(
        IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PMcSOsHoqlz5-CWiZHoTGHN7SQ0p3dSzMJAVuYPVG1E/edit?gid=0#gid=0", "Sheet1!A1:H7"),
        "SELECT Col1, Col4, Col5, Col6, Col7, Col8
         WHERE Col3 = '" & salesman & "'
         LABEL Col1 '" & salesman & "'",
        1
    )
 )

We limit the usage of IMPORTRANGE by only calling it once.¹ Instead of going about it cell-by-cell, using INDEX and MATCH, and since our source range was already in table form, filtering the data is a better and less expensive way of doing this.

Also, this is definitely an XY Problem, i.e. you are asking for help with your solution rather than the problem itself. Unless you are specifically looking for a solution using INDEX and MATCH, use the formula I provided.

---

¹ I suggest that if you will be making a whole load of these summary tables that you move the IMPORTRANGE call into it's own hidden sheet, but this may be unnecessary since Google Sheet might memoize the range asked. This needs testing.