Formula to return a value from a row range
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([202404 Forecast]@row:[202512 Actuals]@row, [202404 Forecast]@row:[202512 Actuals]@row, "202410 Forecast"), 1)
The value collected is 202410 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
Best Answer

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([202404 Forecast]@row:[202411 Forecast]@row, 1, MATCH([Primary Column]@row, [202404 Forecast]$1:[202512 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 rowbased 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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Answers

does this work
=INDEX(COLLECT([202404 Forecast]2:[202512 Actuals]100, [202404 Forecast]1:[202512 Actuals]1, FIND(YEAR(TODAY()) + "" + TEXT(MONTH(TODAY()), "00") + " F", @cell) > 0), 1)
Have not testedThis should return the value "30" from the correct column and row intersection. Adjust the ranges (
2:100
) as needed for your actual data range. 
@AJ Cruz you may or may not find this helpful but my recommendation would be to move away from columnbased data and towards rowbased 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:

Thank you Brent for the quick response
Tried it and got error "unparseable".
Modified to :
=INDEX(COLLECT([202404 Forecast]2:[202512 Forecast]50, [202404 Forecast]2:[202512 Forecast]50, FIND("202410 Forecast", @cell) > 0), 1)and returned "Invalid Value"

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([202404 Forecast]@row:[202411 Forecast]@row, 1, MATCH([Primary Column]@row, [202404 Forecast]$1:[202512 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 rowbased 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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

Thank you Georgie
Not only insightful but helped answer my question to good resolution

Glad I could help, @AJ Cruz!
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!