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

Copper 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!