Nested IF Statement Returning FALSE

[oenleunc]

Ok I've stared at this a really long time and I can't figure out where my missing "else" statement(s) needs to go.

I have 5 columns that *might* have inputs in them, but the maximum number of those 5 that will have inputs is 3. It could be any combination of three out of five.

On a different tab I have three columns (O, P, Q) to capture those three possible inputs; the first column (O) was an easy nested IF statement finding the first column that has an input. No Problem. The next two columns (P and Q) are tougher.

I want my formula to return the value in the first one that has something in it, otherwise jump to the next. If only one (or two) of the columns AG-AK have a value, I want P (or Q) to remain blank.

AG = OpEx
AH = CapEx
AI = Revenue
AJ = FTE
AK = EEX

=IF(O2="OpEx",IF(Tab!AH4<>"","CapEx",IF(Tab!AI4<>"","Revenue",IF(Tab!AJ4<>"","FTE",IF(Tab!AK4<>"","EEX")))),IF(O2="CapEx",IF(Tab!AI4<>"","Revenue",IF(Tab!AJ4<>"","FTE",IF(Tab!AK4<>"","EEX"))),IF(O2="Revenue",IF(Tab!AJ4<>"","FTE",IF(Tab!AK4<>"","EEX")),IF(O2="FTE",IF(Tab!AK4<>"","EEX"),IF(O2="EEX","","")))))

If AK has EEX that means that it's captured in the first (O) column so the P and Q should be blank, and they are, so that works. If anything else is in Column O but AG-AK only has one value, it returns FALSE.

I know I'm surely missing something simple. I tried to put

,""

in between the ending parentheses, but of course that didn't work.

 

[StephenCrump]

Is this what you're trying to do?

	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1
2
3
4	1st	2nd	3rd					Data
5	OpEx	Revenue	EEX					OpEx		Revenue		EEX
6	FTE	EEX									FTE	EEX
7	CapEx	EEX							CapEx			EEX
8	Revenue									Revenue
9
10	With 365
11	OpEx	Revenue	EEX					OpEx		Revenue		EEX
12	FTE	EEX									FTE	EEX
13	CapEx	EEX							CapEx			EEX
14	Revenue									Revenue
15
Sheet1

Cell Formulas
Range	Formula
Z5:AB9	Z5	=IFERROR(INDEX($AG5:$AK5,1+SMALL(IF($AG5:$AK5<>"",COLUMN($AG5:$AK5)-COLUMN($AG5)),COLUMNS($Z5:Z5))),"")
Z11:AB11,Z14,Z12:AA13	Z11	=FILTER(AG11:AK11,AG11:AK11<>"","n/a")
Dynamic array formulas.