QUERY VLOOKUP IMPORTRANGE w/ Google Sheets-CodePudding

I need to perform a VLOOKUP of a variable on Workbook A to find info in Workbook B, hence the IMPORTRANGE. I also need that VLOOKUP to filter based on certain criteria within Workbook B. I've been trying to make QUERY work for me, but it doesn't seem to work with VLOOKUP. I know that FILTER also exists, but I have yet to make FILTER work with VLOOKUP either.

Example:

VLOOKUP(A2,IMPORTRANGE("URL","Sheet2!C:AH"),27,0)

There are duplicate variants to be found in Workbook B/Sheet2 for the variable A2, so i need to set a condition based on Column 24 of Workbook B/Sheet2 to only return data if Column 24 contains the string "FinishProduct". I hope this isn't super easy and I'm just being an idiot. Look forward to the help!!!

New Attempt:

QUERY(
  IMPORTRANGE(
    "1yLZ3H-tW1QaW0fDsK3kb_B96kT9-fV2We15-9KZyTGg",
    "TS Inventory!C:AC"
  ),
  "Select Col27 Where Col1='"&A2:A&"' AND Col22='FinishProduct'"
)

Still didn't work. Produced something but not what I wanted.

CodePudding user response：

try:

=VLOOKUP(A2, QUERY({IMPORTRANGE("URL", "Sheet2!C:AH")}, 
 "where Col22='FinishProduct'", 0), 27, 0)