• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF this equals that OR that or that, then.....

ashfire jon

New Member
Hi all, i tried using this formula at work but couldnt get it to work and was intersted in what you guys would suggest.


I got a work around using normal IF over 5 columns.

Long term solution is to get it back in Access, which isnt my job :).


=IF(F2=OR("AEC","DDH","externals","fec","quality","test & trials","unit 1","unit 1&2","unit 2","unit 3","unit 4","unit 5","unit 6","unit 6&7","unit 7","unit 8","unit amn","unit dgm","unit ferm","unit mrm","unit rtm"),"Construction",IF(F2=OR("electrical w/shop","ms","nbf","paint","plate prep shop","pmf","pps","","sms"),"Manufacturing",IF(F2=OR("nas","nas-annex","nas-outfit"),"Fabrication",IF(F2=OR("none budget","purchase part"),"Other","Another"))))


Thanks
 

Clarity

New Member
If I understand correctly what you are trying to acheive I think this will work:


=if(or(f2="AEC",f2="DDH",f2="externals" etc),"Construction",if(or( etc


A neater and more flexible solution would be to use a VLOOKUP:


If you build a simple two column table. In column one put AEC, DDH, externals etc and in column two put Construction etc. Eg


Table in A5:B20


AEC Construction

DDH Construction

externals Construction

fec Construction

etc

electrical w/shop Manufacturing

ms Manufacturing


Value you are looking up in F2


If you then use VLOOKUP(Lookup value, array, row index number):


=Vlookup(F2,A5:B20,2,false)


And if you wanted to manage the "Another" option (ie not in your list) this can be managed using ISNA:


=if(isna(Vlookup(F2,A5:B20,2,false)),"Another",Vlookup(F2,A5:B20,2,false))


Myles
 

ashfire jon

New Member
bloody hell, i could kick myself. I got so caught up in doing it that way i didnt even think about doing a vlookup....


thanks for realigning me
 
Top