Alternative to Nested IF Statement

Hi all,

I have been a victim of not being able to use the nested IF statement as I have too many IFs. I guess this will not be ideal using IF anyway.

I was wondering if there was another way of getting the result using another Function or VBA possibly.

My current formula looks like this

=IF(E221="LON","London",IF(E221="B","Martlet",IF(E221="KN","Kent",IF(E221="NOR","Northside",IF(E221="NWE","Hillside",IF(E221="RKN","Kent",IF(E221="RS","Roundshaw",IF(E221="RHS","Martlet",,IF(E221="HSH","Southbank"""))))))))

I think I must have about 30 codes in Column E where I need to group them to about 10.

Put the codes and descriptions into a table and then use VLOOKUP.

=VLOOKUP(E221, Sheet2!$A:$B,2,FALSE)

To check for missing entries in Excel 2003:

=IF(ISNA(VLOOKUP(E221, Sheet2!$A:$B,2,FALSE)),"Not found",VLOOKUP(E221, Sheet2!$A:$B,2,FALSE))


Regards, TMS

Hi,

If you put that data into a simple table, you could use a VLOOKUP. So, assuming your codes ("LON", "KN", etc.) are in cells A1:A30 (for example) and the corresponding entries in B1:30, then your formula would be:

=VLOOKUP(E221,$A$1:$B$30,2,0)

Regards

Try this...

Formula:

=VLOOKUP(A4,{"LON","London";"B","Martlet";"KN","Kent";"NOR","Northside";"NWE","Hillside";"RKN","Kent";"RS","Roundshaw";"RHS","Martlet";"HSH","Southbank"},2,0)

Thanks for the rep.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.