Forum Discussion

wrightjj's avatar
wrightjj
Copper Contributor
Jun 18, 2023

range lookup

Am struggling with a range lookup. I have data being pasted from an online table coming in with cells going

PNOV        VALUES

0-100         30%

101-150      60%

151-200      90%

etc. And am wanting to lookup data from one cell in my row if the label matches the table label and then pull data from a cell in that row if the data lies within the range of the table and if so take the value %number. E.g

row to be pulled from with label being PNOV DPMO and cell I want to be referenced being 367:

PNOV DPMO   167

then pulling it from the table with the label PNOV and then taking it from the cell 151-200, as 167 lies between these and then returning the value 90%

 

Apologies if this isn't clear but thank you in advance for any help 🙂

Excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 18, 2023

    wrightjj 

    You can use the XLOOKUP function (depends from your Excel version) to perform the range lookup. The XLOOKUP function simplifies the process and provides a more intuitive way to achieve the desired result.

    Assuming your table with the range values is located in cells A1:B4 (with "PNOV" in column A and the values in column B), and the label you want to match is in cell E2, and the value you want to reference is in cell F2, you can use the following formula:

    =XLOOKUP(F2,VALUE(MID($A$1:$A$4,FIND("-", $A$1:$A$4)+1,LEN($A$1:$A$4))-1),B1:B4)

    Here is a breakdown of how the formula works:

    1. The FIND function is used to locate the position of the dash "-" in the table labels.
    2. The MID function is used to extract the upper range value by taking the substring starting from the position after the dash and continuing until the end of the string.
    3. The LEN function is used to calculate the length of the range values.
    4. The VALUE function is used to convert the extracted range values into numbers for comparison.
    5. The XLOOKUP function is used to find the position of the value from cell F2 within the range of numeric values generated in step 4.
    6. The XLOOKUP function returns the corresponding value from column B.

    Remember to adjust the cell references in the formula to match the location of your data. Once you enter the formula in the desired cell, it should return the expected result based on the range lookup criteria you provided.

    • wrightjj's avatar
      wrightjj
      Copper Contributor
      Jun 18, 2023

      NikolinoDE am on excel 2019 so am unable to use XLOOKUP is there any other way to carry out this formula 

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Jun 18, 2023

        wrightjj 

         

        If you have Excel 2019 (what was not mentioned before), the XLOOKUP function is not available as it was introduced in Excel 365. However, you can still achieve the range lookup using a combination of INDEX and MATCH functions.

        Here is how you can do it:

        Assuming your table with the range values is located in cells A1:B4 (with "PNOV" in column A and the values in column B), and the label you want to match is in cell E2, and the value you want to reference is in cell F2, you can use the following formula:

        swiftCopy code

        =INDEX($B$1:$B$4,MATCH(F2,VALUE(MID($A$1:$A$4,FIND("-", $A$1:$A$4)+1,LEN($A$1:$A$4))-1),0))

        Here's a breakdown of how the formula works:

        1. The FIND function is used to locate the position of the dash "-" in the table labels.
        2. The MID function is used to extract the upper range value by taking the substring starting from the position after the dash and continuing until the end of the string.
        3. The LEN function is used to calculate the length of the range values.
        4. The VALUE function is used to convert the extracted range values into numbers for comparison.
        5. The MATCH function is used to find the position of the value from cell F2 within the range of numeric values generated in step 4.
        6. The INDEX function is used to return the value from the corresponding cell in column B based on the match result.

        Remember to adjust the cell references in the formula to match the location of your data. Once you enter the formula in the desired cell, it should return the expected result based on the range lookup criteria you provided.

        Finally, with your permission, if I may recommend, always include in your question information such as Excel version, operating system, storage medium, etc. This would all benefit.

        Here is a link to it: Welcome to your Excel discussion space!

Resources