Forum Discussion
Excel Worksheet - tried LOOKUP, INDEX, and other functions . . .
How do I retrieve the value from the cell in a worksheet where my horizontal data intersects my vertical data? I tried XLOOKUP, INDEX & some other functions. I have named my ranges and tried the functions both ways - with the range names & with the cells that define the ranges. I feel positive that there is a function/formula that will do this for me.
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.
8 Replies
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.
If you don't mind my asking, can you please explain how this actually works:
... [if] you have row and column ranges identified, they may be intersected by placing them together with only a space separating themI don't recall seeing this intersection method before; is that something specific to LET()?
No, it goes way back to the beginning of Excel. There are three operators that have ranges as operands. The illustration shows:
1) the normal array composition operator, colon ":", that includes the usual way one defines ranges from their first and last cells;
2) the union operator, comma ",", that selects both ranges but rather oddly double counts the intersection;
3) the intersection operator, space " ", that selects cells common to both ranges.
Range operators have always been an underused feature of Excel, or spreadsheets in general. To me, it would make sense that the step before any calculation should be to define the ranges that are to be included within the operations.
=INDEX(Values,MATCH(B15,dates,0),MATCH(B16,clients,0))=SUMPRODUCT((dates=B15)*(clients=B16)*Values)Maybe with these formulas as shown in the attached file. I have named the ranges "dates", "clients" and "values".
