Need help figuring out a formula
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!
Answers

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 AAB 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.
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!