Excel Formulas

Copper Contributor

Hello,

 

I'm looking for some help with excel formulas. 

In column D2 I've got a drop down list, options: Yes, Partial, No, N/A.

If I select Yes in D2, I want I2 to show 2

If I select Partial in D2, I want I2 to show 1

If I select No in D2, I want I2 to show 0

If I select N/A in D2, I want I2 to show N/A.

 

Is this possible?

So far, I managed to get 3 options correctly by using:

 

=IF(D2="Yes","2",IF(D2="Partial","1","0"))

 

Any help would be much appreciated.

 

Kind regards

 

Martyna

2 Replies

@MartynaCzerwinska123 Perhaps not the most straight-forward formula, but you could use:

 

=LOOKUP(D2,{"N/A","No","Partial","Yes"},{"N/A",0,1,2})

 

or

 

=IF(D2="Yes",2,IF(D2="Partial",1,IF(D2="No",0,"N/A")))

 

if you want to stay closer to your initial approach.

Thank you so much Riny! It works perfectly!