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

Brent C. Wilson

does this work

=INDEX(COLLECT([2024-04 Forecast]2:[2025-12 Actuals]100, [2024-04 Forecast]1:[2025-12 Actuals]1, FIND(YEAR(TODAY()) + "-" + TEXT(MONTH(TODAY()), "00") + " F", @cell) > 0), 1)

Have not tested

This should return the value "30" from the correct column and row intersection. Adjust the ranges (2:100) as needed for your actual data range.

ericncarr

@AJ Cruz you may or may not find this helpful but my recommendation would be to move away from column-based data and towards row-based data. Each entry having it's own row makes any sort of lookup, reporting, etc. much much easier whether you're talking about Smartsheet or a Database table. I'm struggling to see what your data represents so I can't give you a very good example but a basic example:

AJ Cruz

Thank you Brent for the quick response

Tried it and got error "unparseable".

Modified to :
=INDEX(COLLECT([2024-04 Forecast]2:[2025-12 Forecast]50, [2024-04 Forecast]2:[2025-12 Forecast]50, FIND("2024-10 Forecast", @cell) > 0), 1)

and returned "Invalid Value"

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

AJ Cruz

Thank you Georgie

Not only insightful but helped answer my question to good resolution

Georgie

Glad I could help, @AJ Cruz!