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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,855
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
38,855
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
Hi & welcome to MrExcel.
What exactly do you have in column A?
I do appreciate your help and thanks for the welcome.
 

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
38,855
Office Version
365
Platform
Windows
Would your values always be followed by either a space or a hyphen?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,919
Messages
5,447,282
Members
405,446
Latest member
morsecode

This Week's Hot Topics

Top