Forum Discussion
Excel Worksheet - tried LOOKUP, INDEX, and other functions . . .
Assuming that your ranges are named 'low' (green), 'high' (red) and 'Est' (brown)
= XLOOKUP(68000,low,Est,,1)
is a column range selected from 'Est'. Similarly
= XLOOKUP(75000,high,Est,,1)
is a row range selected from 'Est'. This row is not just an array of numbers, it is a range reference to a row from your data and, for example,
= ROW(XLOOKUP(75000,high,Est,,1))
will return 9, the row number of the selected range on the worksheet. intersecting the two ranges
= XLOOKUP(68000,low,Est,,1) XLOOKUP(75000,high,Est,,1)returns a reference to the cell which will display as a value of 74. There are several other ways of returning the value including nested XLOOKUPS and INDEX/MATCH combinations.
Note: XLOOKUP requires 2019 or later.
Peter: Thank you for responding to my request for assistance. Like Dub, I've been working in Excel for a long time (about 40 years) and thought I was pretty proficient. I also thought I could figure out how to find the formula I needed.
I tried following your suggestions, but have not achieved the desired results. I'm unfamiliar with the layout you used, and am unsure of what I need to type. Could you type it out (or copy from one of your worksheets) so I can see the whole entry on one line? Here is what the worksheet looks like:
This is the first time I've tried to use the Tech Community to solve an Excel problem. So, I'm not sure if I should, or how I would, e-mail you the worksheet.
I look forward to hearing from you.
Sincerely,
gemmentions
Assuming that your ranges are named 'low' (green), 'high' (red) and 'Est' (brown)
= XLOOKUP(68000,low,Est,,1)
is a column range selected from 'Est'. Similarly
= XLOOKUP(75000,high,Est,,1)
is a row range selected from 'Est'. This row is not just an array of numbers, it is a range reference to a row from your data and, for example,
= ROW(XLOOKUP(75000,high,Est,,1))
will return 9, the row number of the selected range on the worksheet. intersecting the two ranges
= XLOOKUP(68000,low,Est,,1) XLOOKUP(75000,high,Est,,1)
returns a reference to the cell which will display as a value of 74. There are several other ways of returning the value including nested XLOOKUPS and INDEX/MATCH combinations.
Note: XLOOKUP requires 2019 or later.
- Peter: Thank you! I was able to solve my problem with your help.