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)
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 them
I 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.
Well, talk about "live and learn"! I have been using Excel for ages, and I have never heard, let alone used, space as the intersection operator... I guess I have to regroup and rethink.
Really, thanks for the info!