Nested IF condition

[Anthony Slater]

Hi,

I have a NESTED IF formula with five conditions. This is
producing an answer by exclusively satisfying one of the
conditions. Is it by any chance possible to know which of
the five conditions is being satisfied ? (say by returning
a number from 1 to 5 represeting the respective condition)

Alternatively is it possible to assign a colour to the
answer produced depending on which condition was
satisfied ?

Your help in this respect is greatly appreciated

Thanks in advance.

Tony

 

[Andy B]

Hi

It might help if you post your formula and explain why it matters which
option is chosen. There may be a better way to do what you are trying to
achieve.

 

[Anthony Slater]

Hi,

Well basically, the formula is a nested IF(ISERROR(VLOOKUP
to look into 5 sheets. If the first sheet returns an
error, then it will look it up in the second sheet etc etc
until it finds it. Now what matters is the name of the
sheet were it is finding the answer (i.e. the sheet which
produced no error when applying the vlookup formula.)

Thanks,

Tony

 

[Frank Kabel]

Hi
one way:
- copy the formula
- replace the TRUE part of the IF functions (currently
probably a VLOOKUP function) and replace them with the
sheet name

 

[Andy B]

Hi

One way round would be to have a hidden cell, with a similar formula, that
returns the sheet name, rather than the value.

 

[Anthony Slater]

Hi

How do return the sheet name rather than the value?

Tony

 

[Andy B]

Hi

Frank's post explains how to this. Just replace the 'value' bit (that you
are returning in the other cell) with the sheetname.