Nesting Functions

[Nadine]

How do I nest more than 7 functions within a function please? I need to nest
approximately 21 arguments within an 'IF' function.

 

[Otto Moehrbach]

Nadine
You would need VBA for that if that is truly what you need/want to do.
Tell us a bit more about what you have and what you want to do/happen. HTH
Otto

 

[Nadine]

Hello Otto, there probably is a simpler way to do what I'm trying to do. In
a nutshell, I have a spreadsheet where if a certain word (room area) is
entered into one cell/column (eg L10) then the corresponding code needs to be
entered alongside it, (eg K10). Unfortunately we already have thousands of
entries in column L so rather than manually inputting a code into each
corresponding cell in column K I thought I'd put in an if formula similar to
the following as this would also prevent the user from inputting the
incorrect information in column K if they were doing it manually.

This is the formula I started inputting into column K : =IF(L10='Validation
Lists'!E2,'Validation Lists'!D2,(IF(L10='Validation Lists'!E3,'Validation
Lists'!D3,(IF(L10='Validation Lists'!E4,'Validation
Lists'!D4,(IF(L10='Validation Lists'!E5,'Validation
Lists'!D5,(IF(L10='Validation Lists'!E6,'Validation
Lists'!D6,(IF(L10='Validation Lists'!E7,'Validation
Lists'!D7,(IF(L10='Validation Lists'!E8,'Validation
Lists'!D8,(IF(L10='Validation Lists'!E9,'Validation
Lists'!D9,"")))))))))))))))

I was hoping I could just carry on putting in the If arguments until I'd
mentioned all 20 odd room and their corresponding code number, obviously not.

Any ideas?

 

[Alan]

I think what you want to achieve would be more suited to a VLOOKUP table.
It's a lot more simple and avoids the need for very lengthy formulas. This a
link to a tutorial on Debra Dalgleish's web site,
http://www.contextures.com/xlFunctions02.html
Regards,
Alan.

 

[francis]

Hi

I would suggest that you create a table and use a Lookup formula for this
type of tasks, there is Vlookup which will return the result you want and
you don't
need to worry about running into the limitation of the Nested IFs formula

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis