Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-712563%22%20slang%3D%22en-US%22%3EHOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712563%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20I%20really%20Want%20is%3A%20Depending%20on%20Which%20Store%20Name%20I%20enter%20into%20the%201st%20cell%2C%20I%20want%20a%20particular%20%25%20entered%20into%20the%202nd%20cell.%26nbsp%3B%20The%20second%20Cell%20is%20already%20formatted%20to%20be%20a%20percent%20with%20no%20decimals.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20the1st%20Cell%20has%20%22Lowes%22%20in%20it.%26nbsp%3B%20I%20want%20the%202nd%20cell%20to%20end%20up%20with%20%2215%25%22%20in%20it.%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20want%20to%20be%20able%20to%20ad%20additional%20Stores%2C%20with%20their%20perspective%20%25%20as%20well.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20Lowes%20%3D%2015%25%3C%2FP%3E%3CP%3ELeonards%20%3D%2010%25%3C%2FP%3E%3CP%3Eamazon%20%3D%2012%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-712563%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712618%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364246%22%20target%3D%22_blank%22%3E%40Blue-Moon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20Sheet2!B2%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(A2%2C%3CBR%20%2F%3EStorePercent%2C%3CBR%20%2F%3E2%2C0)%3C%2FP%3E%3CP%3ENote%20that%20StorePercent%20is%20defined%20as%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSheet1!%24A%242%3AINDEX(Sheet1!%24B%3A%24B%2CCOUNTA(Sheet1!%24A%3A%24A))%3C%2FP%3E%3CP%3EYou%20can%20add%20stores%20and%20their%20corresponding%20percent%20in%20Sheet1%20without%20any%20required%20modification%20in%20VLOOKUP%20in%20Sheet2!B2.%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20test%20it%20and%20inform%20me%20of%20your%20thoughts%20thereon.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712626%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712626%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364246%22%20target%3D%22_blank%22%3E%40Blue-Moon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20sort%20of%20thing%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20785px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F120009iBEB0854A825C8656%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you'd%20like%20more%20info%20I've%20done%20a%20couple%20of%20videos%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20to%20do%20an%20INDEX%20MATCH%3A%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DcT6K8Gz97PE%26amp%3Bt%3D137s%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DcT6K8Gz97PE%26amp%3Bt%3D137s%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EHow%20to%20do%20a%20drop%20down%20list%20%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DeNmfHsBIyIY%26amp%3Bt%3D24s%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DeNmfHsBIyIY%26amp%3Bt%3D24s%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Blue-Moon
Occasional Visitor

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

@Blue-Moon 

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. 

Please test it and inform me of your thoughts thereon.

@Blue-Moon 

 

This sort of thing?

 

image.png

 

If you'd like more info I've done a couple of videos:

 

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_Cell, Step2_Lookup_Column, 0),0) Also please vote here for a simpler version of INDEX MATCH ...
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 aware of. Be Warned!
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies