If Condition with text and multiple options

[FreddyNoel]

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

[#2 · Accepted solution]

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

[#3]

@FreddyNoel 

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.

Thank you in advance for your time and effort.

Nikolino

I know I don't know anything (Socrates)

[#4]

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

I have learnt something and you made my day.

Best regards

Freddy

[#5]

Thank you, Nikolino, for your quick reply.

My apologies for inserting a picture instead of the file.

This was my first post and I am already learning.

Best regards

Freddy

[#6]

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.

Thank you for your understanding

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

[#7]

@NikolinoDE and @Faraz Shaikh

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

[#8]

@FreddyNoel 

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.

Hope I was able to help you and would be happy to know if I could help you.

Nikolino

I know I don't know anything (Socrates)

[#9]

@NikolinoDE 

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

[#10]

@FreddyNoel 

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.

[#11]

@NikolinoDE 

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

[#12]

I was pleased to be able to help you.
I wish you a nice day / night

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

[#13]

@NikolinoDE 

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

[#14]

@FreddyNoel 

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.

[#15]

@NikolinoDE 

A million thanks again, Nikolino.

You are a Wizard, doing magic with Excel.

Best regards

Frederic

[#16]

Your welcome
I was pleased to be able to help you.

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