find non-#N/A cell in list 1

[johnnygeo]

I have a table like this:

Row   Y1    Y2   ...  Y6    Type
1     N/A   N/A        1     6
2     N/A    1        N/A    2
3      5    N/A       N/A    1

each of the Y1...Y6 columns contains either an N/A or a numeric value. Only one column will contain a number for each row - the others will all be N/A. The Type column is supposed to return which Y column has the numeric value. So in the example, row 1 has a value in column Y6, so the Type is 6.

Is there a way to calculate the Type column without using a huge nested-if expression?

Thanks,
JohnnyGeo

 

[bluedragon2]

You can use a long IF statement like:

=IF(Y1="N/A",IF(Y2="N/A",IF(Y3="N/A","No Match","3"),"2"),"1")

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[yogia]

Hi Johnnygeo:

Following is one way ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[johnnygeo]

yogia,

Thanks, it works fine. For posterity purposes, I changed the type formula to be:

=LOOKUP(7,B2:G2,$B$1:$G$1)

where B2:G2 are the values for the current row, and $B$1:$G$1 are the type column headers.

but what is the significance of using 7 as the lookup value? Is it any numeric value, or should it be a number higher than any of the possible numeric values from the table?

Thanks,
JohnnyGeo

 

[yogia]

Hi Johnnygeo:

but what is the significance of using 7 as the lookup value? Is it any numeric value, or should it be a number higher than any of the possible numeric values from the table?

a number higher than any of the possible numeric values from the table.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com