Forum Discussion
IF AND Help
Hello, I need a cell to auto-populate based on a complicated formula which I cannot figure out. I think it's a nested IF/AND formula, but I don't know how to put it together. Using the tables bel...
=INDEX($B$6:$G$6,MATCH(B2,OFFSET($B$6:$G$6,MATCH(B3,$A$7:$A$14,0),0),1)+1)You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
OliverScheurich Thank you for the response! How do I edit the formula to reference specific cells on other tabs? I need the red cell to be where the AMI level (0-30%, etc.) populates based on the info contained in the blue and orange cells above it (these cells are filled with formulas that reference cells in other tabs). The second image is my data tab which contains various lists and the AMI table. Thanks!
=INDEX(Data!$B$17:$G$17,MATCH(F8,OFFSET(Data!$B$17:$G$17,MATCH(F9,Data!$A$18:$A$25,0),0),1)+1)You are welcome. You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
=F1-F2-F3-F4-F6-F7This is the formula in cell F8 which returns 62747.