More than 64 nested IF functions

[pgft]

Good morning everyone.

I am struggling with a problem that I am not able to solve.

I have an excel sheet in 2010 in which I should make some choices conditioned, nesting the functions "IF".
The problem is that Excel 2010 does not allow more than 64 nesting of "IF", while the possible choices are 127.

In practice, if I have a certain value in cell A2, must appear another certain value in cell B2. And so to the end (at the time the lines are over 132,000).

To give an example of formula (which of course is to be inserted into cell B2):

= IF (A1 = "Rome"; "Italy"; IF (A1 = "London", "England"; IF (A1 = "Beijing", "China"; "Not Defined")))

Going into detail of my spreadsheet, in a column there are some codes, each of which corresponds to the name of a specific location. At the time, filtering the data, I select the empty cells and I manually insert the name of the location in relation to the code present in the column. But I would like to automate the process.

As I said, the problem is that the choices are well 127, while Excel 2010 allows a maximum of 64 nesting "IF".
Wandering on the web, I read of the possibility to use the "CHOOSE", but I did not understand how to proceed.

Could you help me? If it is necessary I could post an example.

Thanks in advance.

[Fotis1991]

Hi

Looks that an INDEX & MATCH should do the trick.

Of course a sample worksheet will help us to help you.

To attach a small sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[pgft]

Hi

Looks that an INDEX & MATCH should do the trick.

Of course a sample worksheet will help us to help you.

To attach a small sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Thank you.

I will try to attach a small file.

Best regards

[Kyle123]

Can't you just use VLookup with a mapping table?

[pgft]

Can't you just use VLookup with a mapping table?

Thanks for your reply.

Unfortunately, I don't speak english well. And I'm not a good developer. I'm sorry.

When you say VLookup, you will say VBA Editor?

Best regards

[Fotis1991]

INDEX & MATCH of my suggestion and VLOOKUP that Kyle123 suggested will do (almost) the same job.

[zbor]

Look here.

Add more Towns/countries in I and J column.

[pgft]

Look here.

Add more Towns/countries in I and J column.

Thanks for your response.

I think I understand the process. I'll try and let you know

Best regards

[Kyle123]

@zbor, that's thoughtful touch for users without IFERROR

[zbor]

You are right. Old habit. I should go with IFERROR

=IFERROR(VLOOKUP(A2;$I$1:$J$1000;2;FALSE);"Not Defined")

[pgft]

Good morning everyone.

I solved the problem building the following macro:

Function myIF(a as variant)
Select Case a
case "condition1"
myIF = "result1"
!
!
!
case "condition127"
myIF = "result127"
End Select
End Function

The only problem is that save operations of the spreadsheet are a bit slow after macro performing, but I think that this is a feature of Excel.
Thanks to all.

[Kyle123]

Much, much easier to have a lookup table as zbor posted...

[pgft]

Much, much easier to have a lookup table as zbor posted...

Hello.

Thanks for the reply. Unfortunately I do not know how to build a lookup table, and the macro seems to work well.

[Kyle123]

Did you download zbors workbook?

[pgft]

Did you download zbors workbook?

Yes, I did it, but it was easier for me to build the macro that I posted.