[Solved] How to write multiple if statement past limit?

[Veda]

Hi, I have a long "IF" formula to make but other than I don't want to write out such a thing the other problem is the limit of "IF" is not enough. I notice from one google search result that there is a IFS function but I can't find that in open office.

What is needed is something like this: =IF(A1=FRED;1;IF(A2=BILL;2;IF(etc... Or using something like IFS(A1=FRED;1;A2=BILL;2;A3=REG;3; etc...

I need to do this x 85 statements so what can I do to achieve this (other than use excel)? Does Libreoffice have the IFS upgrade?

Cheers,
Rob

[FJCC]

You should not use the IF() function for this. VLOOKUP() can do this simply. Make a table where the first column is the names and the second column is the values. If that information is in the range D1:E6, you can then use a formula like

Code: Select all

=VLOOKUP(A1;D1:E6;2;0)

See the attached file for an example. Maintaining a table with 85 entries is far easier than dealing with 85 nested IFs.

[Veda]

You should not use the IF() function for this. VLOOKUP() can do this simply. Make a table where the first column is the names and the second column is the values. If that information is in the range D1:E6, you can then use a formula like

Code: Select all

=VLOOKUP(A1;D1:E6;2;0)

See the attached file for an example. Maintaining a table with 85 entries is far easier than dealing with 85 nested IFs.

Thanks, I did see the vlookup function as an alternative from a google search but didn't understand how that would work. Ok, I will have a go at that, thanks a lot.

[Veda]

Thanks for the replies, I watched a youtube tutorial vid on vlookup and got it to work as required.