IF does not work; can I use Lookup and if yes how?

[Bilyana Gavrilova]

Hi,

I am trying to create a formula...it used to be an IF statement, but now I
need 8 statements and Excel takes up to 7...

Here is what I am trying to create:

IF e1<0 and g1="PC" to result 1020
IF e1>=0 and g1="PC" to result 1021
IF e1<0 and g1="VN" to result 1022
IF e1>=0 and g1="VN" to result 1023
IF e1<0 and g1="BG" to result 1024
IF e1>=0 and g1="BG" to result 1025
IF e1<0 and g1<>"PC",<>"VN",<>"BG" to result 1026
IF e1>=0 and g1=<>"PC",<>"VN",<>"BG" to result 1027


When using the IF and AND function with 7 statements my old formula worked
and now I am trying to find a new way to get the same results.

Any help would be greatly appreciated.

Thank you,

Bilyana Gavrilova

 

[Niek Otten]

Hi Bilyana,

If you insist on one formula, her is one:

=IF(ISNA(VLOOKUP(G1,{"PC",1020;"VN",1022;"BG",1024},2,FALSE)),1026,VLOOKUP(G1,{"PC",1020;"VN",1022;"BG",1024},2,FALSE))+IF(E1>=0,1,0)

But of course it all is much easier to follow if you isolate the VLOOKUP in a separate cell and/or create a table on the worksheet
to VLOOKUP the values instead of having the list in your formula.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I am trying to create a formula...it used to be an IF statement, but now I
| need 8 statements and Excel takes up to 7...
|
| Here is what I am trying to create:
|
| IF e1<0 and g1="PC" to result 1020
| IF e1>=0 and g1="PC" to result 1021
| IF e1<0 and g1="VN" to result 1022
| IF e1>=0 and g1="VN" to result 1023
| IF e1<0 and g1="BG" to result 1024
| IF e1>=0 and g1="BG" to result 1025
| IF e1<0 and g1<>"PC",<>"VN",<>"BG" to result 1026
| IF e1>=0 and g1=<>"PC",<>"VN",<>"BG" to result 1027
|
|
| When using the IF and AND function with 7 statements my old formula worked
| and now I am trying to find a new way to get the same results.
|
| Any help would be greatly appreciated.
|
| Thank you,
|
| Bilyana Gavrilova

 

[Bilyana Gavrilova]

Thanks, Niek! It worked fine.