If is number search formula

romoguy15

New Member
Joined
Mar 24, 2020
Messages
13
Office Version
  1. 365
Platform
  1. 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.
 
Most of the time, there will be a hyphen after GDM-49F. But I would like to write my formula in way that it will still calculate it if there was no formula present like I originally had in the beginning. The specific text is "GDM-49" it will not always be "GDM-49-"
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Let's just say for example that my data in column A is only ever going to be in this format. No hyphens after 49 or 49F

Model #
GDM-49
GDM-72
GDM-49
GDM-72F
 
Upvote 0
If your data was like that you could use a lookup table like
+Fluff.xlsm
ABCDEFGH
1
2GDM-492500GDIM-262000
3GDM-723500GDM-492500
4GDM-492500GDM-49F3000
5GDM-72F4000GDM-723500
6GDIM-26N0GDM-72F4000
7
Summary
Cell Formulas
RangeFormula
B2:B6B2=IFERROR(VLOOKUP(A2,$G$2:$H$6,2,0),0)
 
Upvote 0
Okay, I gave you a bad example so instead I will give you the raw data. This is how it will always look with some cells having a space like the 5th, 6th, and 7th rows below.

Item
GDIM-26NT-HC~TSL01 BLK
GDM-49-HC~TSL01 DOLGEN15 BLK
GDM-72-HC~TSL01 DOLGEN15 BLK
GDM-72 -HC~TSL01 DOLGEN15 BLK
GDM-72 -HC~TSL01 DOLGEN15 BLK
GDM-49F -HC~TSL01 DOLGEN15 BLK
GDM-49-HC~TSL01 DOLGEN15 BLK
GDM-49-HC~TSL01 DOLGEN15 BLK
GDM-49F-HC~TSL01 DOLGEN15 BLK
GDM-49F-HC~TSL01 DOLGEN15 BLK
GDM-72-HC~TSL01 DOLGEN15 BLK
GDM-72-HC~TSL01 DOLGEN15 BLK
GDM-72-HC~TSL01 DOLGEN15 BLK
GDM-49-HC~TSL01 DOLGEN15 BLK
GDM-72F-HC~TSL01 DOLGEN15 BLK
GDM-72F-HC~TSL01 DOLGEN15 BLK
GDM-49F-HC~TSL01 DOLGEN15 BLK
GDM-49F-HC~TSL01 DOLGEN15 BLK
GDM-49F-HC~TSL01 DOLGEN15 BLK
GDM-72-HC~TSL01 DOLGEN15 BLK
GDM-72-HC~TSL01 DOLGEN15 BLK
GDM-49F-HC~TSL01 DOLGEN15 BLK
GDM-72F-HC~TSL01 DOLGEN15 BLK
GDM-49-HC~TSL01 DOLGEN15 BLK
GDM-49F-HC~TSL01 DOLGEN15 BLK
GDM-49F-HC~TSL01 DOLGEN15 BLK
GDM-72-HC~TSL01 DOLGEN15 BLK
GDM-49F-HC~TSL01 DOLGEN15 BLK
GDM-49-HC~TSL01 DOLGEN15 BLK
 
Upvote 0
In that case, how about
+Fluff.xlsm
ADEFGH
1
2GDIM-26NT-HC~TSL01 BLK0GDIM-26-2000
3GDM-49-HC~TSL01 DOLGEN15 BLK2500GDM-49-2500
4GDM-72-HC~TSL01 DOLGEN15 BLK3500GDM-49F-3000
5GDM-72 -HC~TSL01 DOLGEN15 BLK3500GDM-72-3500
6GDM-72 -HC~TSL01 DOLGEN15 BLK3500GDM-72F-4000
7GDM-49F -HC~TSL01 DOLGEN15 BLK3000
8GDM-49-HC~TSL01 DOLGEN15 BLK2500
9GDM-49-HC~TSL01 DOLGEN15 BLK2500
10GDM-49F-HC~TSL01 DOLGEN15 BLK3000
11GDM-49F-HC~TSL01 DOLGEN15 BLK3000
12GDM-72-HC~TSL01 DOLGEN15 BLK3500
13GDM-72-HC~TSL01 DOLGEN15 BLK3500
14GDM-72-HC~TSL01 DOLGEN15 BLK3500
15GDM-49-HC~TSL01 DOLGEN15 BLK2500
16GDM-72F-HC~TSL01 DOLGEN15 BLK4000
17GDM-72F-HC~TSL01 DOLGEN15 BLK4000
18GDM-49F-HC~TSL01 DOLGEN15 BLK3000
19GDM-49F-HC~TSL01 DOLGEN15 BLK3000
20GDM-49F-HC~TSL01 DOLGEN15 BLK3000
21GDM-72-HC~TSL01 DOLGEN15 BLK3500
22GDM-72-HC~TSL01 DOLGEN15 BLK3500
23GDM-49F-HC~TSL01 DOLGEN15 BLK3000
24GDM-72F-HC~TSL01 DOLGEN15 BLK4000
25GDM-49-HC~TSL01 DOLGEN15 BLK2500
26GDM-49F-HC~TSL01 DOLGEN15 BLK3000
27GDM-49F-HC~TSL01 DOLGEN15 BLK3000
28GDM-72-HC~TSL01 DOLGEN15 BLK3500
29GDM-49F-HC~TSL01 DOLGEN15 BLK3000
30GDM-49-HC~TSL01 DOLGEN15 BLK2500
Summary
Cell Formulas
RangeFormula
D2:D30D2=IFERROR(LOOKUP(2,1/(SEARCH($G$2:$G$6,SUBSTITUTE(A2," ","-"))),$H$2:$H$6),0)
 
Upvote 0
If I have to make that little ledger to the side, I am good with it. Thanks for your continuous help.
 
Upvote 0
Making a table for the values you want returned is a lot easier than hardcoding it into a formula.
Also a lot easier to maintain if anything changes
 
Upvote 0
Making a table for the values you want returned is a lot easier than hardcoding it into a formula.
Also a lot easier to maintain if anything changes
You are right. Would you mind explain the LOOKUP(2,1/ in that formula? It is new to me so I would like to understand so I don't just feel like it is copied with no understanding to it.
 
Upvote 0
You don't actually need the 1/ part this will work as well
=IFERROR(LOOKUP(2,(SEARCH($G$2:$G$6,SUBSTITUTE(A2," ","-"))),$H$2:$H$6),0)

The search will return either #VALUE or 1 & the lookup looks up the last value that is less than or equal to 2
 
Upvote 0
Okay so I have entered your formula exactly and it works perfect. I guess I am still trying to understand it.
=IFERROR(LOOKUP(2,(SEARCH($R$2:$R$8,SUBSTITUTE(G2," ","-"))),$U$2:$U$8),0)

Since I removed the 1/ part of the formula, i still don't understand the (LOOKUP(2, before the search

When it is substituting G2, why do we have " ","-" ?

I think I fully understand SEARCH($R$2:$R$8, that is my table of values to look for.

And $U$2:$U$8),0) is my values to substitute with.

IFERROR is easy, if there is any error, it will return a value of zero.



Sorry for the questions, I'm new to using formulas and find it very interesting.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top