If is number search formula

romoguy15

New Member
Joined
Mar 24, 2020
Messages
13
Office Version
365
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
What exactly do you have in column A?
 

romoguy15

New Member
Joined
Mar 24, 2020
Messages
13
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
In that case just add a hyphen to the enter of the search string.
 

romoguy15

New Member
Joined
Mar 24, 2020
Messages
13
Office Version
365
Platform
Windows
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

New Member
Joined
Mar 24, 2020
Messages
13
Office Version
365
Platform
Windows
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

New Member
Joined
Mar 24, 2020
Messages
13
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
Would your values always be followed by either a space or a hyphen?
 

Forum statistics

Threads
1,089,218
Messages
5,406,922
Members
403,112
Latest member
rminor

This Week's Hot Topics

Top