Arrayformula with Vlookup matching Data in range-CodePudding

I am trying to use an ArrayFormula along with Vlookup with a range to match the data from another Worksheet. In my demo, the source sheet with Col B with orange and which is Col A is Stack but the destination sheet is not matching where orange exists. I can't understand where is my fault.

I have tried but it's wrong pulled.

=ARRAYFORMULA(VLOOKUP(B:B,IMPORTRANGE("13UCvlMfCse9A_fyVOPlQiwL6JADKZ3O2kMjUfznI-q0", "Source!A:B")},1))

CodePudding user response：

When using VLOOKUP, the first column of the range must be the search column. And you shouldn't omit the last parameter if you are looking for an exact match.

=ARRAYFORMULA(IFNA(VLOOKUP(B:B,QUERY(TO_TEXT(IMPORTRANGE("13UCvlMfCse9A_fyVOPlQiwL6JADKZ3O2kMjUfznI-q0", "Source!A:B")),"SELECT Col2, Col1"),2,0)))

update

=ARRAYFORMULA(IFNA(VLOOKUP(B:B,MAP({2,1},LAMBDA(col,INDEX(IMPORTRANGE("13UCvlMfCse9A_fyVOPlQiwL6JADKZ3O2kMjUfznI-q0", "Source!A:B"),,col))),2,0)))