Formula to Search Column for Multiple Values

[amyxkatexx]

I am trying to figure out how to write a formula that searches column F for multiple values. Those values will either be Regular, Husky, or Slim and they will be in any random order and with duplications.

Basically, I would like for this formula to search column F and if it finds "Regular", then it returns "100", if it doesn't find "Regular, then it searches for "Husky" and returns "200", and if both "Regular" and "Husky" aren't found, it will search for "Slim" and return "300". If none of these values exist, then it returns "000".

I understand the logic of all this, I just can't seem to figure out the proper function to use to make this work.

Anyone know what to do?

[Marcol]

There is probably a cooler way

However try this

Please Login or Register  to view this content.

Hope this helps

[amyxkatexx]

Okay, so that works great! Quick question though...

I have this formula pasted in three cells, so I could check for each of the three values and return the appropriate number. In the second cell, I deleted the first part where it checks for "Regular" and in the third, I deleted the check for "Regular" and "Husky" ...only problem is, if there is not a "Regular" fit, but there is a "Husky" and a "Slim", it's returning "200" in both the first two cells and "300" in the third. Is there a way for it to check the cell above it so that the order can only go "100", "200", "300" and if "100" doesn't exist, it just skips that? (Also, "100" and "200" might not exist and there might only be "300"....or "100" and "300" might exist, but no "200".)

Basically, I'd like to not have any blank rows, so if there is not a "100", I don't want it to simply not fill in, but actually skip to the next formula and change the next formula to skip to the next formula and so on and so forth.

Does that make sense?

[amyxkatexx]

Attached is an example of what I'm talking about.

I highlighted the 3 cells I am working with in Pink.

[Marcol]

I have tried to follow your workbook, but it's not to easy to understand.

Why so many IF statements, similar to this?.

Please Login or Register  to view this content.

This states that, if B5 is not empty then use that value, and if it is then use it all the same.
Maybe just use.....

Please Login or Register  to view this content.

You then create more if statements to return a certain value, and then create another to return the original value based on the answer, I'm lost.....

You have created lookup tables, but never seem to use them.

I'm sorry, but until you rationalise your data, I cannot see how I can help you further.

[amyxkatexx]

Sorry, I am so much in the middle of testing everything right now.

I have started so many ideas and then given up on them because I found a better way. I am currently working on yet another version of this, so hopefully it will be clean enough for you to help me because this issue still remains and I can't seem to get past it.

I'll add on to this when I have something a little more cleaned up.

Thanks for your time thus far though!

[amyxkatexx]

And btw, those formulas are necessary.

Please Login or Register  to view this content.

...For instance refers to the brand. It is not just returning B5 whether it is empty or not, it is only returning B5 if there is a value in B5, otherwise it is returning nothing. If I just use...

Please Login or Register  to view this content.

...then whenever there is not a value in B5, a "0" is returned and that is no good.

The other formulas really shouldn't be too much of a problem here, but I'm thinking now that trying to accomplish this without blank rows is going to be damn near impossible when you consider all the if statements I would have to include to account for the many different scenarios.

I'll keep you updated, but it looks like this is going to be solved another way. I was told to stay away from macros, but I might be able to get around that with an addin so that the actual macros aren't on this workbook.

[Marcol]

I would go down the Lookup Tables route, if I were you

Have a look at this link
http://www.cpearson.com/excel/TablesAndLookups.aspx

It will save a great deal of head scratching in the long run.

Have fun......

[amyxkatexx]

That's a great reference tool. I have that saved in my favorites.

So, I think I may have figured out how I'd like to do this, but I'm struggling yet again.

I was thinking, if I have all my formulas on a separate sheet and then simply use a formula to show them on the users end, and use a lookup or possibly index...or both to only return the rows with a value in column A.

To explain my concept further...

Essentially, after the user is done filling out the information in the colored cells at the top, they then copy and paste special values of what appears below row 40 into a new spreadsheet. (I am going to try and automate this part with a macro, but my boss seems to be against that.)

The part that you don't see yet is that these formulas will have to generate a lot of blank rows to account for all the possibilities that one might have in each model they build. I would have no problem just using the macro to copy from the last row over to the new spreadsheet and then deleting the blank rows, but since I am trying to stay away from macros as much as possible, I was trying to simply have no blank rows to begin with by using really long and silly formulas that just make everything cluttered.

So if I move all my formulas to a new sheet in this workbook, would there be a way to visually show only the rows with data in column A on the "Boys' 4-7 Apparel" worksheet below row 40? This way the user won't accidentally miss any data when copying/pasting to their blank worksheet and we don't have to go back and delete blank rows when finished either.

Does that make sense now? Sorry this is so confusing...the rules I have to follow that I think are a bit unnecessary are what's really getting in the way here, but people are used to what's comfortable and it's hard to change their minds. I'm sure you know how that is...

[amyxkatexx]

I found this, which would work except for the fact that COUNTA counts the cell if there is a formula in it. Is there something else I could do to modify this so it only counts it if it has an actual value in it?


http://forums.techguy.org/business-applications/570609-how-remove-blank-cells-excle.html <--It's the third to last response that I am using.

[amyxkatexx]

I found my answer! On another thread!

Here is the link...

http://www.excelforum.com/excel-prog...e-results.html


...Freaking awesome!

Thanks for all your help Marcol!!