Can Index Match be put inside Vlookup

[Muzztang]

I'm having troubles figuring out how to put Index Match inside of Vlookup. If I type out my formula as:

=IF(A24="","",VLOOKUP((VLOOKUP(A24,Items!$A:$C,3,FALSE)),'[MasterOrders.xlsx]06102018-145622-Ninja'!$A:$C,3,FALSE))

it works correctly. The problem I am at is the table_array. [MasterOrders.xlsx] always stays the same, but the 06102018-14622-Ninja changes based on the lookup_value.

If I put:

=INDEX($P$2:$P$7,MATCH((VLOOKUP(A24,Items!$A:$K,11,FALSE)),$S$2:$S$7,0)) into a cell it finds the correct value (06102018-14622-Ninja etc...) But I don't understand how to put the Index Match into the Vlookup. This is what I have and it comes up with #VALUE

=IF(A24="","",VLOOKUP((VLOOKUP(A24,Items!$A:$C,3,FALSE)),"[MasterOrders]""&(INDEX($P$2:$P$7,MATCH((VLOOKUP(A24,Items!$A:$K,11,FALSE)),$S$2:$S$7,0)))&!$A:$C",3,FALSE))

Greatly appreciate any help. Thank you in advance.

 

[Oscar Cronquist]

Try this:

=IF(A24="","",VLOOKUP((VLOOKUP(A24,Items!$A:$C,3,FALSE)),INDIRECT("[MasterOrders]"&(INDEX($P$2:$P$7,MATCH((VLOOKUP(A24,Items!$A:$K,11,FALSE)),$S$2:$S$7,0)))&!$A:$C),3,FALSE))

The INDIRECT function allows you to build a text string and use that as a cell reference.
https://support.office.com/en-us/article/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261