HOW TO: "If cell contains specific text then return specific text"

Occasional Visitor

HOW TO: "If cell contains specific text then return specific text"

What I really Want is: Depending on Which Store Name I enter into the 1st cell, I want a particular % entered into the 2nd cell.  The second Cell is already formatted to be a percent with no decimals.

So if the1st Cell has "Lowes" in it.  I want the 2nd cell to end up with "15%" in it.

And want to be able to ad additional Stores, with their perspective % as well.

So Lowes = 15%

Leonards = 10%

amazon = 12%

2 Replies

Re: HOW TO: "If cell contains specific text then return specific text"

In the attached file, the formula in Sheet2!B2 is:

=VLOOKUP(A2,
StorePercent,
2,0)

Note that StorePercent is defined as:

=Sheet1!\$A\$2:INDEX(Sheet1!\$B:\$B,COUNTA(Sheet1!\$A:\$A))

You can add stores and their corresponding percent in Sheet1 without any required modification in VLOOKUP in Sheet2!B2.

Re: HOW TO: "If cell contains specific text then return specific text"

This sort of thing?

How to do an INDEX MATCH:  https://www.youtube.com/watch?v=cT6K8Gz97PE&t=137s

How to do a drop down list : https://www.youtube.com/watch?v=eNmfHsBIyIY&t=24s

A 3 minute lesson in how to do INDEX MATCH the easy way.Here's the iii shortcut formula for you to copy =INDEX( Step3_Result_Column, MATCH( Step1_Lookup_C...
Creating a drop down list using an Excel Table is an easy way to set up drop downs that automatically pick up new items. However, there are 2 traps to be a...