Forum Discussion
Solved
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 fu...
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.
