Query data in SheetA where column matches SheetB-CodePudding

I have 2 sheets, SheetA and SheetB. The Rows in each sheet are unsorted but there is a 1:1 map of Columns A on each sheet. I am trying to do something like sql, where i say: =select B from SheetA where SheetA.name == SheetB.A2

SHEETA
name, age
foo,   20
gary,  30

--------
SHEETB
name, hobby,  age
gary,  kites, =select B from SheetA where SheetA.name == A2
foo,   boats,  ...

I was trying the query but that returns lists, which actually would be what i wanted, but the data is not expected to be sorted.

CodePudding user response：

Like some LOOKUP? You can try with:

=XLOOKUP(A2,'Sheet B'!A2:A,'Sheet B'!B2:B ,,0)

And, if you want for the whole column'

=INDEX(XLOOKUP(A2:A,'Sheet B'!A2:A,'Sheet B'!B2:B ,,0))