Home

HOW TO: if cell contains text then output additional text to another cell

%3CLINGO-SUB%20id%3D%22lingo-sub-722722%22%20slang%3D%22en-US%22%3EHOW%20TO%3A%20if%20cell%20contains%20text%20then%20output%20additional%20text%20to%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722722%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20set%20up%20a%20way%20that%20when%20one%20of%20the%20four%20options%20in%20a%20drop%20down%20list%20is%20selected%20that%20the%20cell%20two%20columns%20over%20will%20output%20specific%20data%20about%20that%20option.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20four%20ice%20trailers%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELittle%20Blue%3C%2FP%3E%3CP%3ELow%20Profile%3C%2FP%3E%3CP%3EBig%20Blue%3C%2FP%3E%3CP%3EHatchback%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20list%20for%20them%20is%20under%20cell%20B16.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20have%20cell%20D16%20output%20the%20length%2C%20width%2C%20height%2C%20and%20capacity%20of%20the%20trailer%20selected%20under%20cell%20B16.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELittle%20Blue%20-%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E10'9%22Lx6'Wx7'3%22H%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E125%2010%23%20Bags%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELow%20Profile%20-%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E13'2%22Lx5'10%22wx5'11'H%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E125%2010%23%20Bags%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBig%20Blue%20-%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E12'7%22Lx6'10%22Wx8'4%22H%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E210%2010%23%20Bags%20or%20100%2020%23%20Bags%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHatchback%20-%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E14'7%22Lx7'5%22Wx8'1%22H%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E420%2010%23%20Bags%20or%20200%2020%23%20Bags%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20using%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISNUMBER(SEARCH(%2210.75%20FT%20Long%20x%206%20FT%20Wide%20x%207.25%20FT%20Tall%20and%20Holds%20125%2010%23%20Bags%20of%20Ice%22%2C%22Little%20Blue%22))%2C%22Little%20Blue%22%2C%2210.75%20FT%20Long%20x%206%20FT%20Wide%20x%207.25%20FT%20Tall%20and%20Holds%20125%2010%23%20Bags%20of%20Ice%22)%3CBR%20%2F%3Eor%3CBR%20%2F%3E%3DIF(ISNUMBER(SEARCH(%2213.1667%20FT%20Long%20x%205.833%20FT%20Wide%20x%205.917%20FT%20Tall%20and%20Holds%20125%2010%23%20Bags%20of%20Ice%22%2C%22Low%20Profile%22))%2C%22Low%20Profile%22%2C%2213.1667%20FT%20Long%20x%205.833%20FT%20Wide%20x%205.917%20FT%20Tall%20and%20Holds%20125%2010%23%20Bags%20of%20Ice%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20have%20had%20no%20success.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20cleanish%2Fstreamlined%20way%20to%20get%20the%20output%20to%20draw%20what%20I%20want%20from%20each%20selection%20in%20the%20input%20cell%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-722722%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-722912%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20if%20cell%20contains%20text%20then%20output%20additional%20text%20to%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722912%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367288%22%20target%3D%22_blank%22%3E%40Todd_Sines%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20trying%20something%20like%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20890px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F120779i124D066423C68C58%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22indexMatch.gif%22%20title%3D%22indexMatch.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ED3%20%3D%20INDEX(H4%3A%24H%247%2CMATCH(B3%2CtblTrailer%5BTrailer%5D%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725006%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20if%20cell%20contains%20text%20then%20output%20additional%20text%20to%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725006%22%20slang%3D%22en-US%22%3EYes.%20Something%20very%20similar%20to%20that.%20I've%20just%20started%20trying%20to%20do%20more%20with%20Excel%20and%20it%20is%20definitely%20a%20learning%20process%20for%20me.%20I%20think%20that%20I%20should%20be%20able%20to%20get%20it%20all%20put%20together%20based%20off%20of%20your%20response.%20Thank%20you!%3C%2FLINGO-BODY%3E
Todd_Sines
New Contributor

I am trying to set up a way that when one of the four options in a drop down list is selected that the cell two columns over will output specific data about that option.

 

I have four ice trailers:

 

Little Blue

Low Profile

Big Blue

Hatchback

 

The list for them is under cell B16.

 

I want to have cell D16 output the length, width, height, and capacity of the trailer selected under cell B16.

 

Little Blue - 

10'9"Lx6'Wx7'3"H

125 10# Bags

 

Low Profile - 

13'2"Lx5'10"wx5'11'H

125 10# Bags

 

Big Blue - 

12'7"Lx6'10"Wx8'4"H

210 10# Bags or 100 20# Bags

 

Hatchback - 

14'7"Lx7'5"Wx8'1"H

420 10# Bags or 200 20# Bags

 

I have tried using :

 

=IF(ISNUMBER(SEARCH("10.75 FT Long x 6 FT Wide x 7.25 FT Tall and Holds 125 10# Bags of Ice","Little Blue")),"Little Blue","10.75 FT Long x 6 FT Wide x 7.25 FT Tall and Holds 125 10# Bags of Ice")
or
=IF(ISNUMBER(SEARCH("13.1667 FT Long x 5.833 FT Wide x 5.917 FT Tall and Holds 125 10# Bags of Ice","Low Profile")),"Low Profile","13.1667 FT Long x 5.833 FT Wide x 5.917 FT Tall and Holds 125 10# Bags of Ice")

 

and have had no success.

 

Is there a cleanish/streamlined way to get the output to draw what I want from each selection in the input cell?

2 Replies

@Todd_Sines -

 

Are you trying something like: 

indexMatch.gif

 

 

D3 = INDEX(H4:$H$7,MATCH(B3,tblTrailer[Trailer],0))

Yes. Something very similar to that. I've just started trying to do more with Excel and it is definitely a learning process for me. I think that I should be able to get it all put together based off of your response. Thank you!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies