too long a formula, can someone cut it down

[Johnnyboy5]

I need to use the IF 53 times ! I have 53 rows which I am using filter
and Subtotal (row70) I need to get one of the columns "C" to enter
its text (names) into the subtotal row 70.

However I can only use the brackets up to 9 times ( I think) can does
anyone know how to reduce the formula size. I need to get A70 up to
=53 and likewise with the C column.

=IF(A70=3,C3,IF(A70=4,C4,IF(A70=5,C5,IF(A70=6,C6,IF(A70=6,C6,IF
(A70=7,C7,IF(A70=8,C8,
IF(A70=9,C9))))))))


Row 70 works like this
if the subtotal in column A row 70 = 5 then in the subtotal column C
(where the above formula is) it puts the name of the person, all the
other subtotal columns are for data and they work fine. This does
what I need but I need but only up to number 9, I need to get the
"equals" up to number 53 and therefore the C part of the formula up to
53.

thanks everyboy how reads these posts.

 

[muddan madhu]

Try this

=IF(AND(A70>=3,A70<=53),INDIRECT("C"&A70))

 

[Johnnyboy5]

Try this

=IF(AND(A70>=3,A70<=53),INDIRECT("C"&A70))

You are a STAR... thank you so much it works really well...thanks
again, you have saved me from going mad

 

[Gary''s Student]

=INDIRECT("C" & A70)

You don't need any IF's
Just use the value in A70 as in index into column C.


You could also use either OFFSET() or INDEX()

 

[Johnnyboy5]

=INDIRECT("C" & A70)

You don't need any IF's
Just use the value in A70 as in index into column C.

You could also use either OFFSET() or INDEX()

Hi just tested - it works just as well, thank you as well, you to
are a STAR

 

[Rick Rothstein]

Given that OFFSET and INDIRECT are both volatile functions, I think I would
opt for the non-volatile INDEX function...

=INDEX(C1:C9,A70)

 

[Johnnyboy5]

Given that OFFSET and INDIRECT are both volatile functions, I think I would
opt for the non-volatile INDEX function...

=INDEX(C1:C9,A70)

Hi, tried it but it didn't work in the workbook I have set up.

 

[Johnnyboy5]

Hi,  tried it but it didn't work in the workbook I have set up.

All I get is #REF! is there something else I should be adding to the
index formula ?

 

[Rick Rothstein]

Please try to follow the same posting method as those that respond to you
(in this case, top post your new messages), it makes it easier for people
who come across this thread in a Google search to follow.

Now, as to your response that the INDEX formula doesn't work... I don't see
how that can be as the formula I posted produces the same results as the one
Gary''s Student posted.

 

[Rick Rothstein]

Did you change the C1 in my formula? The range has to start at C1 in order
for the index to count down to the correct cell.