Search code examples
arraysgoogle-sheets-formulavlookupspreadsheetarray-formulas

Extracting data with Vlookup and checking values with Arrayformula

I need to extract data from one tab (extracted data) to another tab and validate the data in the following way:

if 0% assign 3 if from 0 till -10% assign 2 if from -10% and more assign 1 if from 0% till 10% assign 4 if from 10% and more assign 5

here is the link to the file https://docs.google.com/spreadsheets/d/1f8SFi2hNP6Anav7G7BYWyK-fasPk1pT1A2HFJblT-FI/edit?usp=sharing

Solution

  • I suggest you use two vlookups.

    If you have a tab called 'Ranges' with the following two columns:

    Percentage  Result
-1000%      1
-10%        2
0%          3
10%         4
11%         5

    enter image description here

    Then the formula in cell B1 on the 'calculations' tab would be something like:

    =arrayformula({"Con Potential";iferror(vlookup(vlookup(A2:A,'Extracted data'!A:D,4,0),Ranges!A:B,2,1),)})

    Delete all data below cell B1 for the arrayformula to work correctly.

    The second vlookup references col D on the 'Extracted data' tab because that is the percentage I think you are comparing? If not, alter 4 in the vlookup to another column.

    enter image description here

    If it helps, please see: https://stackoverflow.com/help/someone-answers

    NB: In place of Ranges!A:B you could use a fixed array:

    =arrayformula({"Con Potential";iferror(vlookup(vlookup(A2:A,'Extracted data'!A:D,4,0),{-10,1;-0.1,2;0,3;0.1,4;0.11,5},2,1),)})

    If you want to temporarily see the fixed array in case you want to edit any values, place this in a cell somewhere out of the way:

    ={-10,1;-0.1,2;0,3;0.1,4;0.11,5}

    , is used to bump to a new column, ; is used as a return.

    Relevance

    Looking at 'Relevance' lookup from 'Position Delta' and this table in your sheet:

    enter image description here

    Since a 'position delta' value of 10 cannot both have a relevance of 5 and 4, I've made the assumption that 10 gets 5. If that is incorrect, then I'll adjust the boundaries.

    Add this to cell C1 on the 'calculations' tab (clearing all cells below):

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

    The fixed array {0,5;11,4;21,3;31,2;41,1;51,0} has these values:

    0   5
11  4
21  3
31  2
41  1
51  0

    If you need to change the boundaries so 10 is a 4, not 5, then change the vlookup to use this fixed range {0,5;10,4;20,3;30,2;40,1;50,0}:

    0   5
10  4
20  3
30  2
40  1
50  0

    vlookup is incremental and anything up to 11 will get 5, then 11 to 20 will get 4, 21 to 30 will get 3 and so on.

    ,1) in the vlookup at the far right gets the nearest value match until 'position delta' has reached the next boundary.