CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
Stella
Stella

Reputation: 25

Arrayformula with Vlookup matching Data in range

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))

Worksheet demo

I am expecting in the destination sheet that Col A matches Col B.

Upvotes: 0

Views: 153

Answers (2)

z..
z..

Reputation: 12823

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)))

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36840

With dynamic array formula MAP(), try-

=MAP(B1:INDEX(B1:B,COUNTA(B1:B)),LAMBDA(x,IFERROR(QUERY(INDEX(TO_TEXT(IMPORTRANGE("13UCvlMfCse9A_fyVOPlQiwL6JADKZ3O2kMjUfznI-q0", "Source!A:B"))),"select Col1 where Col2='" & x & "'"),"")))

enter image description here

Upvotes: 0

Related Questions