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.
I pretty standard approach is to extract row and column indices and then use the INDEX function to return the cell.
= LET(
selectRowIdx, XMATCH(foreignKey,primaryKey),
selectColIdx, XMATCH(hdr,headers),
INDEX(data, selectRowIdx, selectColIdx))Since you say you have row and column ranges identified, they may be intersected by placing them together with only a space separating them
= LET(
selectedRow, XLOOKUP(foreignKey,primaryKey,data),
selectedColumn, XLOOKUP(hdr,headers,data),
selectedRow selectedColumn)
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.