# How to find the value of one cell from looking at three columns

Options
edited 05/25/23

Hi everyone I am trying to create a formula where it utilizes three columns to pull a value of one cell from the row.

Ultimately what I am trying to accomplish is create an formula where there are three columns in a report (Year, Month, and Average Amount)

Every month a new row will be added and a formula I am trying to create a formula in a new cell to feed into a dashboard

The Formula will first look for the specific year so for example 2023, and then it will look for the most current month which would be numbered through 1-12. After it finds the row with the most current month it will pull the value from the Average Amount Column every time a new month is added is there a formula that can do this?

This is what I currently have so far

=IF((([year],2023) AND if([month], 11)), [Average_Amount]@[month]row))

• ✭✭✭✭✭✭
Options

If new rows are being added to the bottom of the sheet, you can use:

=INDEX([Average_Amount]:[Average_Amount], COUNTIFS([Average_Amount]:[Average_Amount], @cell <> ""))

• ✭✭✭✭✭✭
Options

So you are going to need a helper cell. I suggest to put it in the summary and call it today and just put this formula in it:

=TODAY()

Then you will need to reference that cell in your formula:

=IF(AND(Year@row="2023",Month@row=MONTH(Summary Cell)),[Average_Amount]@row)

You don't need brackets around any column that is a single word.

Not 100% sure that formula will work, but this will get you on a better path.

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!