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