Help with formula to search 2 cells and produce a result

[champion777]

Hello,

I'm new to excel and came up with this mostly from the internet:

=IF(OR(ISERROR(AND(FIND("265",D2,1),FIND("GAL",G2,1)))=TRUE,E2,(E2*265)),IF(ISERROR(AND(FIND("275",D2,1),FIND("GAL",G2,1)))=TRUE,E2,(E2*275)))

Basically I'm trying to compare D2 and G2 and either display E2, or E2 multiplied by 265/275. I also need to do this for 330 as well, however I can't get 2 to work right so i haven't tried with 3. If G2 has GAL in it and D2 has 275 I want it to display E2*275. When D2 is 265 or 330 I want E2*265 or E2*330 respectively, but only when G2 has GAL in it. If any of these pairs of requirements are not met I need it to simply display the value in cell E2.

When I test the above formula it seems to work for the 275 pairing, but not for 265. If I switch the 265's and 275's it displays E2*265 properly. It seems the back portion of the formula works so why doesn't the front side?

If you have any other formula's or know how to fix this one please let me know.

PS If you have a fix that also does 330 that would be awesome.

Thank you

[FDibbins]

if the values you are working with are just 265, GAL etc and the values ARE actual values, and they are not part and a longer string of text, try removing the "find" and just reference them with and(D2=265,g2="Gal")

Also, a vlookup consists of "if criteria is matched", "do this", "do that" your formula does not have the "do that" part at the end

=IF(OR(ISERROR(AND(FIND("265",D2,1),FIND("GAL",G2,1)))=TRUE,E2,(E2*265)),IF(ISERROR(AND(FIND("275",D 2,1),FIND("GAL",G2,1)))=TRUE,E2,(E2*275)),do that)

if this doesnt help, maybe you could upload a sample for me to look at?

[champion777]

Desc Type # units here Unit total Container
Corrosion Inhib. 02692, IBC, P275-1W 2 550 GAL

---------- Post added at 07:36 AM ---------- Previous post was at 07:03 AM ----------

Please ignore last post, I did it by accident and couldn't edit it

D E F G
Row 1 Desc Type # units here Unit total Container
Row 2 IBC, P275-1W 2 550 GAL


Here's an example. I'm using find because the 275/265/330 is within the cell D description. The formula would go in Column F. I have various containers for G, but I'm only interested in GAL. This row is an example of the formula working, but when applied to other cells with 265 it doesnt work.

=IF(OR(ISERROR(AND(FIND("275",D2,1),FIND("GAL",G2,1)))=TRUE,E2,(E2*275)),IF(ISERROR(AND(FIND("265",D 2,1),FIND("GAL",G2,1)))=TRUE,E2,(E2*265)))

Like I said before, if I swap the "find" numbers to the above, it then works for 265 and not 275.

It seems like the part here in yellow is the one not displaying correctly. I tested it by changing the E2 to another cell. It changed nothing, because it was still using the last part of the formula not the first =TRUE statement. (This i ran for multiple lines, some saying 265 and some saying 275)

=IF(OR(ISERROR(AND(FIND("265",D2,1),FIND("GAL",G2,1)))=TRUE,E2,(E2*265)),IF(ISERROR(AND(FIND("275",D 2,1),FIND("GAL",G2,1)))=TRUE,E2,(E2*275)))

Is the 2nd part that reads =TRUE,E2,(E2*___) overriding the first one?

The "do that" part is already in there..Perhaps i formatted it wrong, but displaying either E2 or E2 multiplied by 265/275/330 depending on the FINDs is what i want to be the result of this.

Sorry if this is hard to understand, it's hard for me to explain when I know so little about Excel..

---------- Post added at 07:41 AM ---------- Previous post was at 07:36 AM ----------

Sorry for the formatting on that first part, i do not know why it wont work!

D contains a description for example "P275-1W"
G is the container AKA "GAL"
E is the Quantity I input
F is the formula

[FDibbins]

could you uploads a sample workbook for me to look at please?

[champion777]

Had a guy at work look at it, and he showed me another way to do it. It resulted in:

=IF(AND(ISNUMBER(SEARCH("265",D22,1)),ISNUMBER(SEARCH("GAL",G22,1))),E22*265,IF(AND(ISNUMBER(SEARCH("275",D22,1)),ISNUMBER(SEARCH("GAL",G22,1))),E22*275,IF(AND(ISNUMBER(SEARCH("330",D22,1)),ISNUMBER(SEARCH("GAL",G22,1))),E22*330,E22)))

He used search and isnumber instead of find/iserror and also nested the next search in the "false" part of the IF. So it would look for 265/gal and if it found it, it would display, if not it would search 275, etc.

Neat way of doing it, never occured to me as my brain doesnt work like that.

Thanks for looking at this though!