Index Match Indirect - Help!

I give up! I'm trying to match 3 variables (one being a sheet name) but I cannot get it right. I have a Summary sheet, where you select a Sheet name, and B3 should return the value from looking in the Sheet found in B1, matching the data crossing A3 and B3. Here is what I have so far:

=INDEX(INDIRECT("'"&$B$1&"'!$C$2:$Z$99"), MATCH(A3,INDIRECT("'"&$B$1&"'!$C$2:$Z$99"),), MATCH($B$2,INDIRECT("'"&$B$1&"'!$C$2:$Z$99"),))

I have attached the workbook for easier assistance. If I can just get B3 going, I will be able to complete the rest! If the value is blank, I'd like it to return a blank if possible.

Note: Not that it matters I'm sure, but B1 and B2 - J2 are dropdowns so you can select who you want to look at and for what month.

Change the formula in B3 of the Summary sheet to this:

=INDEX(INDIRECT("'"&$B$1&"'!$C$2:$Z$99"), MATCH(B$2,INDIRECT("'"&$B$1&"'!$C$2:$C$99"),0), MATCH($A3,INDIRECT("'"&$B$1&"'!$C$1:$Z$1"),0))

You can copy this down to get other values for Joe Smith. You can also copy it across for other people, but as you have no more data in the Oct2019 sheet, you will only get #N/A errors, unless you change to Nov2019.

Note also that you do not have a heading for Voice TPG, so that row also produces errors.

Hope this helps.

Pete

Fantastic! I will look at the changes you made to make sure I understand them for next time.
Regarding the column headers, yeah, I have some cleanup to do. I'm rebuilding this from an old metrics sheet my department uses.

Thanks again!!

The main things were:

MATCH only works on a 1-D array

The syntax for INDEX is INDEX(table, row, column), and you had the row and column parameters the wrong way around.

I used partial absolute addressing for B$2 and $A3, to enable you to copy across and down.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Ok, that makes sense. One other thing - I'm getting some N/As.. what is needed at the end to ensure it results as blank?

You need to wrap the formula with IFERROR, like this:

=IFERROR(INDEX(INDIRECT("'"&$B$1&"'!$C$2:$Z$99"), MATCH(B$2,INDIRECT("'"&$B$1&"'!$C$2:$C$99"),0), MATCH($A3,INDIRECT("'"&$B$1&"'!$C$1:$Z$1"),0)),"")

Hope this helps, and thanks for the rep.

Pete