SOLVED

# If Condition with text and multiple options

Occasional Contributor

# If Condition with text and multiple options

Hello,
I am new to formula and trying to create one that shows a different result in the column Score "E" every time a number is entered in the Stars column "D".
If 5 = Gold, if 4 = Silver, picked up from the B5, B4, B3 cells, etc...

I'll be grateful if someone could help with the right formula?

Thank you.
Freddy

15 Replies
best response confirmed by FreddyNoel (Occasional Contributor)
Solution

# Re: If Condition with text and multiple options

Hi @FreddyNoel ,

There are many ways to handle it, please find below you may construct your formula using VLOOKUP. your source data should be in ascending order to get the results.

=VLOOKUP(D4,\$A\$9:\$B\$13,2,TRUE)

Attached is the sample file for your reference

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

# Betreff: If Condition with text and multiple options

If I may recommend you add a file (without sensitive data) to show your project instead of a picture.
You have to consider that the helper has to make the effort to design the file that already exists.
This is very tiring from the start, so it is always good to insert a file.
This way you will be helped faster and the helpers too, whereby they can give a faster and more tailored answer.

Nikolino

I know I don't know anything (Socrates)

# Re: If Condition with text and multiple options

Thank you, Faraz, for the quick reply and awesome solution.

I have learnt something and you made my day.

Best regards

Freddy

# Betreff: If Condition with text and multiple options

My apologies for inserting a picture instead of the file.

This was my first post and I am already learning.

Best regards

Freddy

# Betreff: If Condition with text and multiple options

Everything is OK. You have received from Mr. Faraz Shaikh a proposed solution that suits you, as far as I have seen. I can't expect better than that.

Nevertheless, I would recommend adding a file (without sensitive data) the next time.

Nikolino
I know I don't know anything (Socrates)

# Betreff: If Condition with text and multiple options

Thank you both for your input and if I may ask one more favour, it would make my spreadsheet amazing. As per Nikolino recommendation, I attach a file for easier editing.

Is there a possibility to add to the "=VLOOKUP(D9,\$A\$16:\$B\$20,2,1)" a complementary formula that would make my Gold rating cell coloured Yellow, Silver rating cell coloured Grey and Bronze rating cell coloured Brown? The colour could either be the text or the fill-in cell.

Thank you again for your amazing support.

Best regards

FreddyNoel

# Betreff: If Condition with text and multiple options

In the inserted file you can see the formula in action.

in German:

=WENNFEHLER(SVERWEIS(D10;\$A\$16:\$B\$20;2);"")

in English:

=IFERROR(VLOOKUP(D10,\$A\$16:\$B\$20,2),"")

So you no longer have an error message ... if there is no value in D then E is also empty.

Nikolino

I know I don't know anything (Socrates)

# Betreff: If Condition with text and multiple options

Awesome! Thank you so much, Nikolino for this unsolicited tip which makes my spreadsheet look neat and so much more professional!

I had no idea one could do that, and again learnt something brilliant today.

I am still wondering if Excel can do the function of adding colour for my Gold, Silver and Bronze cells? Maybe that is not possible, and it would not be a problem if I cannot do that.

Thank you again for your great knowledge and kind assistance.

Best regards

FreddyNoel

# Betreff: If Condition with text and multiple options

Here is the file with the desired colors.

At the same time information on how to do conditional formatting in Excel

# Use conditional formatting to highlight information

https://support.microsoft.com/en-us/office/use-conditional-formatting-to-highlight-information-fed60...

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

# Betreff: If Condition with text and multiple options

Hello, Nikolino,

Sorry for my late acknowledgement as I had a busy day.

FANTASTIC! I am in awe by your knowledge and kind contribution to making my spreadsheet an absolute gem.

I would not have been able to do it without your help.

The link to the Conditional Formatting is brilliant and very clear.

A million thanks again.

Best regards

FreddyNoel

# Betreff: If Condition with text and multiple options

I wish you a nice day / night

Nikolino
I know I don't know anything (Socrates)

# Betreff: If Condition with text and multiple options

Hello Nikolino,

I am stuck again after finding out that I made a mistake with my ratings.

If you can help again, please, I would be much grateful.

In the column Total M, each member's Total should be the sum of the TWO HIGHEST Scores ONLY, out of 3 marks.

For example, Member 1 Total should be 1.25 and Member 2 shall be 2.50

I have tried but lamentably failed to find the right formula.

Thank you if you can offer a solution.

Best regards

Frederic

# Betreff: If Condition with text and multiple options

Add this formula into cell M4 and drag the cell down (to the next cell, etc.)

See the attached file.

German formula:

=KGRÖSSTE(J4:J6;1)+KGRÖSSTE(J4:J6;2)

English formula:

=LARGE(J4:J6,1)+LARGE(J4:J6,2)

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

# Betreff: If Condition with text and multiple options

A million thanks again, Nikolino.

You are a Wizard, doing magic with Excel.

Best regards

Frederic