If is number search formula

[romoguy15]

Hello,

I am having a small issue with this formula I've been trying to figure out. I have in Column A a list of different models of coolers. In column B, I want the formula to generate a price based on each model that matches in column A. The formula that I am using is below.

=IF(ISNUMBER(SEARCH("GDIM-26",A2)),"2000",IF(ISNUMBER(SEARCH("GDM-49",A2)),"2500",IF(ISNUMBER(SEARCH("*GDM-49F*",A2)),"3000",IF(ISNUMBER(SEARCH("GDM-72",A2)),"3500",IF(ISNUMBER(SEARCH("GDM-72F",A2)),"4000",)))))

My issue that I am having is, when Column A contains GDM-49F, it still shows the same value for GDM-49 even though GDM-49F is $3000 This is also happening for GDM 72. If column A contains GDM-72F, it will show the same value as GDM-72. I hope I am explaining this well. Basically, those models, 49, 49F, 72, and 72F need to be I guess unique to their own.

 

[Fluff]

Hi & welcome to MrExcel.
What exactly do you have in column A?

 

[romoguy15]

Hello, In column A I have

GDIM-26NT-HC~TSL01 BLK

GDM-49-HC~TSL01

GDM-72-HC~TSL01

GDM-72-HC~TSL01

GDM-72-HC~TSL01

GDM-49F-HC~TSL01

GDM-49-HC~TSL01

GDM-49-HC~TSL01

GDM-49F-HC~TSL01

 

[Fluff]

In that case just add a hyphen to the enter of the search string.

 

[romoguy15]

That worked perfectly thank you. But what if my data does not have those hyphens? Some of the data is all over the place and I just want it to pick up only GDM-72 or GDM-49F. Could I add to this formula a NOT state or something along those lines?

 

[romoguy15]

Let me rephrase that, my data will always have a hyphen after the letters GDM. That will be constant. But it will not always have a hyphen after 49 or 72 or 49F

 

[romoguy15]

Hi & welcome to MrExcel.
What exactly do you have in column A?

I do appreciate your help and thanks for the welcome.

 

[Fluff]

it will not always have a hyphen after 49 or 72 or 49F

In that case I'm not sure how you would be able to exact matches only.

 

[romoguy15]

I am honestly puzzled how the hyphen after makes the data unique, but the F after the 49 or 72 does not make it unique. I am hoping to solve because the data further down on some does not include hyphens after.

 

[Fluff]

Would your values always be followed by either a space or a hyphen?