Formula to return a value from a row range

AJ Cruz

I need help to define a formula that returns the value at a column x row intersect

I modified a formula found in the community that returns the column header in row 1 but have not been able to determine how to convert that into a column reference to be used with @cell or @row to return the appropriate value. The formula is as follows…

=INDEX(COLLECT([2024-04 Forecast]@row:[2025-12 Actuals]@row, [2024-04 Forecast]@row:[2025-12 Actuals]@row, "2024-10 Forecast"), 1)

The value collected is 2024-10 F (so right column, wrong row), however what I need returned is the value 30

Error received "Invalid Value"

Your help or guidance greatly appreciated

Georgie

Hi @AJ Cruz,

I’d use an INDEX(MATCH) formula to do this. Since the data we’re looking to match is displayed horizontally, we can add a ‘1’ into our formula to denote the row index. So, your formula would be:

=INDEX([2024-04 Forecast]@row:[2024-11 Forecast]@row, 1, MATCH([Primary Column]@row, [2024-04 Forecast]$1:[2025-12 Actuals]$1, 0))

However, since we need to use cell references to reference the first row, we can’t convert this to a column formula. It can be dragged down to other rows and will apply the correct value for each row - but it won’t be automatically applied to new rows.

In order to create a formula that can be applied as a column formula, I’d follow @ericncarr’s suggestion above of moving towards row-based data. If the data was displayed in this format, we’d be able to reference an entire column within our formula - eliminating the need to include cell references - and we’d then be able to convert it to a column formula. For more on column formulas, see our article here: Use column formulas to apply calculations to all rows in a sheet.

Hope that helps!

Georgie