SOLVED

Excel Worksheet - tried LOOKUP, INDEX, and other functions . . .

Copper Contributor

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.

8 Replies

@gemmentions 

=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".

@gemmentions 

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()?

@TheDub 

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.

image.png

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!

@Peter Bartholomew 

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:

gemmentions_0-1646336471467.png

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

best response confirmed by gemmentions (Copper Contributor)
Solution

@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.
1 best response

Accepted Solutions
best response confirmed by gemmentions (Copper Contributor)
Solution

@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.

 

View solution in original post