Forum Discussion
wrightjj
Jun 18, 2023Copper Contributor
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 wan...
NikolinoDE
Jun 18, 2023Gold Contributor
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:
- The FIND function is used to locate the position of the dash "-" in the table labels.
- 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.
- The LEN function is used to calculate the length of the range values.
- The VALUE function is used to convert the extracted range values into numbers for comparison.
- 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.
- 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.
- wrightjjJun 18, 2023Copper Contributor
NikolinoDE am on excel 2019 so am unable to use XLOOKUP is there any other way to carry out this formula
- NikolinoDEJun 18, 2023Gold Contributor
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:
- The FIND function is used to locate the position of the dash "-" in the table labels.
- 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.
- The LEN function is used to calculate the length of the range values.
- The VALUE function is used to convert the extracted range values into numbers for comparison.
- 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.
- 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!
- wrightjjJun 18, 2023Copper Contributor
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