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]

The substitute replaces any spaces in G2 with a hyphen, that way we can search for GDM-49- or GDM-49F-
One of the best ways to understand what a formula is doing is to use the "Evaluate Formula" feature on the formula tab.

 

[romoguy15]

Thanks, that is useful to an extent. I guess it does not actually explain the use of the number 2 in (LOOKUP(2, because I can manipulate that number to a 3 or a 15 and still get the same result.

 

[Fluff]

The 2 is just an "arbitrary" number. The lookup will look for the the last value that is less than or equal to 2.
As the search terms are always at the start of the string, the search will always return 1.
HTH