IF statement formula

[David_j78]

I am trying to write out an IF statement to that looks back through several cells checking if each is blank and going back to the next until I hit a cell with an amount it in.

I have been able to write it to check at least 3 cells back, but then I get stuck.

Formula I have so far is:
=IF(Sheet1!$AS7="",IF(Sheet1!$AP7="",Sheet1!$AM7,Sheet1!$AP7),Sheet1!$AS7)

Essentially I need to the formula to say if AS7 is blank, give me AP7. If AP7 is blank give me AM7, AM7 blank give me AJ7, so forth and so on.
My order of precedence is AS7, AP7, AM7, AJ7, AG7, AD7, AA7, X7, U7, R7, O7, L7, I7, and finally F7.

 

[NateSC]

Try an IFS so you can write them all as independent tests, but it stops at the first TRUE result. This will also let you easily change the order, add cells, or remove cells without having to deal with a massive nested IF.

=IFS(AS7<>"",AS7,AP7<>"",AP7,AM7<>"",AM7,AJ7<>"",AJ7,AG7<>"",AG7,AD7<>"",AD7,AA7<>"",AA7,X7<>"",X7,U7<>"",U7,R7<>"",R7,O7<>"",O7,L7<>"",L7,I7<>"",I7,F7<>"",F7,TRUE,"NO VALUES FOUND")

 

[dreid1011]

Are you trying to return the first value you find searching in reverse order? Is there data in the cells between each target cell? What values are stored in the target cells?

Depending on your answers, you could use XLOOKUP.

 

[Fluff]

How about

=LET(c,CHOOSECOLS(F7:AS7,SEQUENCE(,14,40,-3)),TAKE(FILTER(c,c<>""),,1))

 

[David_j78]

How about

=LET(c,CHOOSECOLS(F7:AS7,SEQUENCE(,14,40,-3)),TAKE(FILTER(c,c<>""),,1))

Thanks Fluff! That seemed to work!

Never though of using IFS that way!

 

[Fluff]

Glad we could help & thanks for the feedback.