range lookup

[wrightjj]

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

[#2]

@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.

[#3]

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

[#4]

@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!

[#5]

@NikolinoDEI have attached a screenshot of all the data layed out in the format according to how you have entered it into the formula however I am still having it return #N/A apologies if this is an easy fix