IF Functions

[crispybadger]

Hi,

Can some please help me to add for than one IF function to one cell.

I have started by putting a basic function in the attached. But it can only give me the correct answer if its true or false.

I want to have multiple posibilities to say yes. I.E:-

Is a test required for certain parts? Well the answer is YES for RRU, Hybrid, Micro and Pico, but no for RFU.

Hopefully the attached sheet can explain better than I can

Many Thanks in advance

[TMS]

You could use OR to test for multiple conditions or, if you have a table set up, you could use VLOOKUP.

Regards, TMS

[Spencer101]

I think as you have a "key" already then a VLOOKUP would be the best bet, if I've understood what you're after correctly that it.

Try entering the below formula in cell B2 on your example attachment and dragging down to B6.

=VLOOKUP(A2,$A$14:$B$18,2,FALSE)

Does that do what you want?

[crispybadger]

Hi,

Thank you for the reply, this does work well for the attached sheet, but unfortuantley I was just using that as an exapmle to show you what the results should be.

I cannot have the key in the sheet Can you suggest another function please?

Many Thanks

[Spencer101]

Could you give us a slightly bigger sample workbook?

We'd need to know all possible entries and their Yes/No answers.

[crispybadger]

Hi,

For the time being that will be all the entries I need.

Once I see the formula, I can add to it as and when.

Many Thanks

[martindwilson]

=LOOKUP(A2,{"HYBRID","MICRO","PICO","RFU","RRU"},{"Yes","Yes","Yes","No","Yes"})
this bit {"HYBRID","MICRO","PICO","RFU","RRU"} must be sorted ascending
i never understand the i cant have the key in the sheet bit, it can always go in another sheet and hidden if need be

[Spencer101]

I agree with MDW above, the key stored somewhere and hidden would make life much easier. Both to write the formula AND to maintain it when new values are added.

Using the LOOKUP supplied would be much easier than a string of embedded IFs (although it can be done that way).

[crispybadger]

Hi,

Thanks for the reply.

But I cannot hand the tracker over to the customer with hidden cells and surplus sheets unfortuantley. So could you tell me the formula for the IF string Please

[icestationzbra]

you could use @mdw's LOOKUP formula to get the same results as the one below:

Please Login or Register  to view this content.

or this VLOOKUP:

Please Login or Register  to view this content.

[Spencer101]

And here is the pointlessly clunky nested IF:

=IF(A2="RFU","No",IF(A2="RRU","Yes",IF(A2="HYBRID","Yes",IF(A2="MICRO","Yes",IF(A2="PICO","Yes","")))))

Although with the specific data you have provided, you could also use:

=IF(A2="RFU","No","Yes")

[martindwilson]

But I cannot hand the tracker over to the customer with hidden cells and surplus sheets unfortuantley.

of course you can how would they know? anyway you have been provided with 5 answers including mine which you completely ignored.

[martindwilson]

i received rather strange pm.

Why does it matter to you if I can or cannot hand it back to the customer?? No hidden formulas or extra sheets! End of.

I appreciate your help with the formulas but not the constant questioning of the scope of works I am working too

op seems to think criticism/suggestions are out of order.. well best you learn excel before committing to a client something you obviously cannot do.
if i didn't know better i'd think it was my mrs having a bad day

[Spencer101]

The minute the word "customer" was mentioned by OP I became suspicious...
There's something just not quite right about it all.

I get the feeling this thread should have been titled "Nested IF... How?" or something similar....

[crispybadger]

Haha 2 different people have had a go in regards to an Excel question. I'm not sure why it bothers you so much, you both obviously know excel far better than me, so sorry if you felt my question was below your excel standards.

Of course I don't know excel, otherwise I wouldn't be asking simple questions.

I was under the impression that this forum was designed for people like me to come and ask questions and hopefully learn and maybe one day help someone else, maybe I was wrong.

I have met some really helpful people on here and have learnt a lot. So thank you to them

[TMS]

Most people answering questions on the forum want to help and give advice ... even if you don't feel you want or need it.

No one wants to have a go at you ... but if you PM someone to tell them to back off, it sometimes sends the wrong message.

You can see from the number of posts Martin has made that he has the knowledge and experience to make valid and practical suggestions. Maybe, you would benefit from listening ... the questions are to really understand your problem not to wind you up.

Regards, TMS

[Spencer101]

Nobody is "having a go" at anyone.

There were several suggestions given, all of which were a better solution than the one you seemed to be alluding to. If nested IFs were what you needed to know for your customer's requirements then all you had to do was say.

I struggle to think of any reason you couldn't use a hidden lookup table (or even a visible one).

Anyway, I hope you have your solution now.

S.