CODEWITHSUNDEEP
arraysgoogle-sheets-formulavlookuparray-formulas
Hanna
Hanna

Reputation: 135

Arrayformula with vlookup - Range Issue

I am trying to range the Data from the tab "Extracted data" in the tab "Action Items" Conversion Potential column (Action items tab) is taking the data from CTR Delta (Extracted data tab). Relevance column (Action items tab) is taking the data from Position Delta (Extracted data tab).

For some reasons, it simply does not range the data in the right way.

Conversion Potential Range should go like: enter image description here CTR Range -2 1 -1 2 0 3 1 4 2 5

Relevance is expected to be ranged like:

enter image description here Range Result 0-10 5 10-20 4 20-30 3 30-40 2 40-50 1 50-100 0

https://docs.google.com/spreadsheets/d/1MlyMmZ3JcWmYeDS86YB7yQnTsb7Fv0btYa7b4o-zwgo/edit#gid=1011640328

Upvotes: 0

Views: 60

Answers (1)

Aresvik
Aresvik

Reputation: 4620

For 'Action Items'!M3 (delete cells below):

=arrayformula({"Conversion Potential";iferror(vlookup(vlookup(D4:D,'Extracted Data'!A:N,14,0),{-2,1;-1,2;0,3;1,4;2,5},2,1),)})

For 'Action Items'!N3 (delete cells below):

=arrayformula({"Relevance";iferror(vlookup(vlookup(D4:D,'Extracted Data'!A:N,13,0),{0,5;11,4;21,3;31,2;41,1;51,0},2,1),)})

enter image description here

Upvotes: 1

Related Questions