NESTED IF or LOOKUP formula?
 

I have a spreadsheet with NAME in col A and dates in Cols B-N. Under column
A, I have a list of the names. The names have been asked to mark which date
they would like by putting an X in the appropriate col next to their name.
This has given me a grid showing the names and their choice of date.
However, I now want to mark their choices in another single column, which
will show the actual date they have chosen. Rather than type each one out,
I'd like to use a formula that looks along the row next to the name and
returns the date from the column where the X lies. But how to do? Pls help,
thanks, Annie.

NESTED IF or LOOKUP formula?
 

Unfortunatly, Excel allows up to seven levels of nested IF functions.
Fortunatly, there is a way to go around it.

Try this if there is no "x" in the cell, nothing will be returned:

=IF(B2="x",$B$1,"")&IF(C2="x",$C$1,"")&IF(D2="x",$ D$1,"")&IF(E2="x",$E$1,"")&IF(F2="x",$F$1,"")&IF(G 2="x",$G$1,"")&IF(H2="x",$H$1,"")&IF(I2="x",$I$1," ")&IF(J2="x",$J$1,"")&IF(K2="x",$K$1,"")&IF(L2="x" ,$L$1,"")&IF(M2="x",$M$1,"")&IF(N2="x",$N$1,"")

It work for me!
--
Yanick


"Annie1904" wrote:

I have a spreadsheet with NAME in col A and dates in Cols B-N. Under column
A, I have a list of the names. The names have been asked to mark which date
they would like by putting an X in the appropriate col next to their name.
This has given me a grid showing the names and their choice of date.
However, I now want to mark their choices in another single column, which
will show the actual date they have chosen. Rather than type each one out,
I'd like to use a formula that looks along the row next to the name and
returns the date from the column where the X lies. But how to do? Pls help,
thanks, Annie.

NESTED IF or LOOKUP formula?
 

Assuming the dates are in row 1, out in column O or beyond, try this formula.
Put it in the same row with the first name and fill down:
=INDEX(A$1:N$1,1,MATCH("x",$A2:$N2))
Be sure to format the column with this formula as a date.


"Annie1904" wrote:

I have a spreadsheet with NAME in col A and dates in Cols B-N. Under column
A, I have a list of the names. The names have been asked to mark which date
they would like by putting an X in the appropriate col next to their name.
This has given me a grid showing the names and their choice of date.
However, I now want to mark their choices in another single column, which
will show the actual date they have chosen. Rather than type each one out,
I'd like to use a formula that looks along the row next to the name and
returns the date from the column where the X lies. But how to do? Pls help,
thanks, Annie.

NESTED IF or LOOKUP formula?
 

Thank you - both useful formulas that work. I have gone with JLatham's
because it's shorter, though!

"JLatham" wrote:

Assuming the dates are in row 1, out in column O or beyond, try this formula.
Put it in the same row with the first name and fill down:
=INDEX(A$1:N$1,1,MATCH("x",$A2:$N2))
Be sure to format the column with this formula as a date.


"Annie1904" wrote:

I have a spreadsheet with NAME in col A and dates in Cols B-N. Under column
A, I have a list of the names. The names have been asked to mark which date
they would like by putting an X in the appropriate col next to their name.
This has given me a grid showing the names and their choice of date.
However, I now want to mark their choices in another single column, which
will show the actual date they have chosen. Rather than type each one out,
I'd like to use a formula that looks along the row next to the name and
returns the date from the column where the X lies. But how to do? Pls help,
thanks, Annie.