URGENT HELP Too many nesting levels!!!

[HazelOCR]

Hi please help.
I have the below formula but it has too many nesting levels:
=IF(C4="1st",'Step 1 Materiailty Assessment'!B25,IF(C4="2nd",'Step 1 Materiailty Assessment'!B26,IF(C4="3rd",'Step 1 Materiailty Assessment'!B27,IF(C4="4th",'Step 1 Materiailty Assessment'!B28,IF(C4="5th",'Step 1 Materiailty Assessment'!B29,IF(C4="6th",'Step 1 Materiailty Assessment'!B30,IF(C4="7th",'Step 1 Materiailty Assessment'!B31,IF(C4="8th",'Step 1 Materiailty Assessment'!B32,IF(C4="9th",'Step 1 Materiailty Assessment'!B33,IF(C4="10th",'Step 1 Materiailty Assessment'!B34,IF(C4="11th",'Step 1 Materiailty Assessment'!B35)))))))))))

I need a cell D3 to display an answer based on a selection from a dropdown box in C4 (1st -11th). The Answer in C4 will correspond to a value (text) from a range of cells in another Sheet ('Step 1 Materiality Assessment' B25:B35)

 

[FormR]

Welcome to the forum!

You could try:

=INDEX('Step 1 Materiailty Assessment'!$B$25:$B$35,LEFT(C4,LEN(C4)-2))

 

[HazelOCR]

That is working!!! Thank you so much for the quick response I must remember that formula will come in handy

 

[AlanY]

try this

=INDEX('Step 1 Materiailty Assessment'!$B$25:$B$35,MATCH('Step 1 Materiailty Assessment'!C4,{"1st","2nd","3rd","4th","5th","6th","7th","8th","9th","10th","11th"},0),0)

 

[FormR]

That is working!!! Thank you so much for the quick response I must remember that formula will come in handy

Great, good to hear