I need a formula that looks at cells and returns column name?

Christopher Flemings

In Cell 2 A, I would like a formula that looks at Cells in row two from Skilled to Float, and Then return the column name if any of those cells containing a numerical text.

So the formula should read something like this if Cell 2B contains text then, Skilled, if not then if Cell 2C contains text then MCDD, If not then if Cell 2D contains Text then INT, and so on. If any of the cells in the row are blank then there should be nothing in cell 2A.

Here is where I run into problems with a simple nested if statement. It is possible to have more than one number in a cell across the row like the sample above, and the formula should then list the name of the cell that the last numerical value shows up in so in the example above the return should be INT NOT Skilled.

In addition to that the last three cells in the row I, J, and K, should all return the same "text" of ASL. Which would be an if statement like this: if cell I or J or K contains text, then ASL which is a simple If statement. Also these three cells will only have values in any of the three cells and none of the cells prior to I will contain numerical text of any kind except for the cell with the formula in it or 2A.

I do not know if I need a helper column to help deviate between what the If statement is looking to return or not?

Genevieve P.

Hi @Christopher Flemings

It sounds like you just need to make sure the order of your IF statements is correct (see: IF Function). A Nested IF statement will stop as soon as it finds a "true" statement and won't read the rest of them, which means that if you start with your "Skilled" column then it won't read any of the other statements when there's a value in that cell.

Instead, start with your end columns - if they're blank, then move backwards through the list until it finds the furthest column to the right to return data.

In your statements, all you have to do is see if the cell is "not blank", or: <> ""

So, lets start with your I, J, K columns, or what I believe are titled #AZ, #Reg, and Float. (See: Create a Cell or Column Reference in a Formula)

=IF(OR([#AZ]@row <> "", [#Reg]@row <> "", Float@row <>""), "ASL",

Then we can move back to the next column:

=IF(OR([#AZ]@row <> "", [#Reg]@row <> "", Float@row <>""), "ASL", IF(SMHRF@row <> "", "SMHRF",

And move back again...

=IF(OR([#AZ]@row <> "", [#Reg]@row <> "", Float@row <>""), "ASL", IF(SMHRF@row <> "", "SMHRF", IF(CLF@row <> "", "CLF",

Until we have a full formula checking each cell:

=IF(OR([#AZ]@row <> "", [#Reg]@row <> "", Float@row <>""), "ASL", IF(SMHRF@row <> "", "SMHRF", IF(CLF@row <> "", "CLF", IF(SHL@row <> "", "SHL", IF(ICFDD@row <> "", "ICFDD", IF(INT@row <> "", "INT", IF(MCDD@row <> "", "MCDD", IF(Skilled@row <> "", "Skilled", ""))))))))

Cheers,

Genevieve

Genevieve P.

Hi @Christopher Flemings

It sounds like you just need to make sure the order of your IF statements is correct (see: IF Function). A Nested IF statement will stop as soon as it finds a "true" statement and won't read the rest of them, which means that if you start with your "Skilled" column then it won't read any of the other statements when there's a value in that cell.

Instead, start with your end columns - if they're blank, then move backwards through the list until it finds the furthest column to the right to return data.

In your statements, all you have to do is see if the cell is "not blank", or: <> ""

So, lets start with your I, J, K columns, or what I believe are titled #AZ, #Reg, and Float. (See: Create a Cell or Column Reference in a Formula)

=IF(OR([#AZ]@row <> "", [#Reg]@row <> "", Float@row <>""), "ASL",

Then we can move back to the next column:

=IF(OR([#AZ]@row <> "", [#Reg]@row <> "", Float@row <>""), "ASL", IF(SMHRF@row <> "", "SMHRF",

And move back again...

=IF(OR([#AZ]@row <> "", [#Reg]@row <> "", Float@row <>""), "ASL", IF(SMHRF@row <> "", "SMHRF", IF(CLF@row <> "", "CLF",

Until we have a full formula checking each cell:

=IF(OR([#AZ]@row <> "", [#Reg]@row <> "", Float@row <>""), "ASL", IF(SMHRF@row <> "", "SMHRF", IF(CLF@row <> "", "CLF", IF(SHL@row <> "", "SHL", IF(ICFDD@row <> "", "ICFDD", IF(INT@row <> "", "INT", IF(MCDD@row <> "", "MCDD", IF(Skilled@row <> "", "Skilled", ""))))))))

Cheers,

Genevieve