VLOOKUP, QUERY?

[KHI1350]

Hi, I have 6 sheets, and they have the same structure. I want to find the specific value (in Row R) according to the ID (in Row A) among these 6 sheets. 

I tried Query, and Vlookup, but It did not go well.

When I tried Vlookup, there was a problem writing the range, because the range should be all six sheets.

What would be the best formula for my situation.

I really appreciate any help you can provide.

[#2 · Accepted solution]

QUERY() is google sheet function not Excel. In Excel you can use VSTACK() and XLOOKUP(). VSTACK() can stack all values into one array from different sheets like =VSTACK(Sheet1:Sheet6!B2:B100)