Formula help

Amanda Z

Hello, I'm new to formulas in Smartsheet and I'm having difficulty adapting from Excel's formulas.

Budget Formula help with:

IF the (spend category) column on the main budget sheet matches the (spend category) on the January sheet then return the (actual amount) in the (January) column on the main budget sheet.

iLoveData

@Amanda Z

A formula framework you can use would be along the lines of:

IF([Spend Column - Main Sheet]@row = {January Sheet Reference matching main sheet}, {January Sheet Referencing Actual Amount}, "")

When you're entering in your formula you'll see a hyperlink asking you to reference another sheet, this will let you open up the January one and import the information you need for the criteria and the value if true. Value if false can be blank by imputing "" just like excel or any other value you want in there to fit your needs.

Hope this helped!

Amanda Z

Hello Michael, It don't work for me, I don't think I explained it well. Please see the below picture.

iLoveData

@Amanda Z

That picture helped a lot! I was able to replicate your issue and the best solution, in my opinion, will be an index match function.

In the example you provided, the formula will reside within the January Colum of Main Sheet: Actuals. The INDEX range will be the cell range from Second Sheet: Reports-January. Make sure to select reference external sheet to properly pull in data from your second sheet.

The first MATCH function will want to match the value in Spend category in Main Sheet against the Spend Category in your Second Sheet. You'll want to do this for each line which is why we are checking each row ([Spend Category]@row) against the spend category range in your Second Sheet.

Since it looks like there are multiple spends per category in your Second Sheet. I recommend using the SUMIF function to create subtotals and make sure those subtotals are the ranges you pull in. You can do this either in the same sheet or create a subtotal Sheet or use sheet summaries to create a summarized sheet.

The column index is just what column the $ value is.

Hope this helps!