• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Alternative for nestedif

A

abhi2611

Member
Could Someone help me with an alternative for a nestedif in excel? Say for example..I have a list of employees...If there age is less then 10, I need the output to be A....if the age is less than 20...I need the output to be B and so on...
 
Why not to create a table with age and correspondent value and search this table using VLOOKUP function?

Upload a file here and possible your answer will arrive quicker
 
Hello Abhi,
Try:
=LOOKUP(15, {0;10;20;30;40}, {"A";"B";"C";"D";"E"})

here 15 is the age

Change to cell references as needed.

Cheers,
Sajan.
 
Hey Sajan,

Thanks for the reply....if the age ranges and outputs ranges are in two separate columns, will this formula still work?
 
Hi,
Yes, it would. Feel free to upload a sample workbook and I would be happy to take a look.

Cheers,
Sajan.
 
Hey Sajan,

I came across another scenario where I have three columns, Based on the first two I need an output in the third column.

Scenario:

Columns AB&C,

If Column A&B =1&1 give me W
If Column A&B =1&0 give me X
If Column A&B =0&1 give me Y
If Column A&B =0&0 give me Z

How would you address this without nestedif.

Thank you.
 
Hi SirJB7,

Thank you for the response. The solution you gave worked but is there a way to reference the ouput to a column instead of typing "Z","Y"....etc?

Thanks again!!
 
Hi, abhi2611!
Replace each letter by the cell reference to that letter & "1". Ok, it's shorter to write than explain:
=IFERROR(CHOOSE(A1*2+B1+1,Z1,Y1,X1,W1),"")
=CHOOSE(MAX(MIN(5,A1*2+B1+1)),Z1,Y1,X1,W1,"")
where the 1st. one could be too this:
=IFERROR(INDEX(W1:Z1,1,5-CHOOSE(A1*2+B1+1),"")
Regards!
 
You must log in or register to reply here.
Back
Top