Mar 01 2022 12:36 PM
Mar 01 2022 12:36 PM
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.
Mar 01 2022 01:04 PM
Maybe with these formulas as shown in the attached file. I have named the ranges "dates", "clients" and "values".
Mar 01 2022 01:10 PM
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)
Mar 01 2022 05:17 PM
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()?
Mar 02 2022 02:24 AM
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.
Mar 02 2022 06:31 AM - edited Mar 02 2022 06:32 AM
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!
Mar 03 2022 11:44 AM
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.
Mar 03 2022 01:35 PMSolution
Assuming that your ranges are named 'low' (green), 'high' (red) and 'Est' (brown)
is a column range selected from 'Est'. Similarly
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,
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.