Need help figuring out a formula

MonicaL

Hello,

I'm using Smartsheet this year for our financial aid awards. Previous years it was done through excel using a Financial Aid grid. We take the family size and income and then see where the fall in the grid for their FA award. Last year it was done with an extremely long formula of =IFERROR(IF(X2=1,VLOOKUP(AB2,'FA Grid'!$D$31:$P$52,13,1),IF(X2=2,VLOOKUP(AB2,'FA Grid'!$E$31:$P$52,12,1),IF(X2=3,VLOOKUP(AB2,'FA Grid'!$F$31:$P$52,11,1),IF(X2=4,VLOOKUP(AB2,'FA Grid'!$G$31:$P$52,10,1),IF(X2=5,VLOOKUP(AB2,'FA Grid'!$H$31:$P$52,9,1),IF(X2=6,VLOOKUP(AB2,'FA ETC. Any help on how I can recreate this or something similar would be great.

Thank you!

James Keuning

Do you know how VLOOKUPS work? Because if not, this is going to be hard for you, but if you do know, then you need to create two sheets.

The MAIN sheet, the one with the formula, will probably have a bunch of columns, at least A-AB which is what... 28?, and the value in X is what's determining which column in the LOOKUP table is being returned (12, 10, 11, etc). So the value in the column AB is being searched in the FA GRID, and the column from which the match is picked from FA GRID is determined by the value in X.

So you need to create another sheet with whatever data is in your FA grid, specifically in column/row D31 through P52.

From there the nested IF statements and the VLOOKUPS work very similar in Smartsheet to how Excel works. You say Etc... I assume that it drills down through columns 8, 7, 6, down to 1.

I think you need to get one VLOOKUP working in Smartsheet, and one IF statement. Figure these out using any kind of data. Some generic data is better, because you can post some screen shots here. And once you figure out the VLOOKUP and the IF statements, you should be able to complete the next step, which is nesting them.