Using Search, Index, Lookups, Etc.

I think I'm making this way harder than it probably is. I need to search for certain text within a field. If it is found I want to apply a value when found.

For example.

Column A Is the column I'm searching
A1 - 123 Jason
A2 - New York
A3 - Atlantic Ocean

Column B Is the column I'm comparing against or searching for
B1 - Jason
B2 - York

Column C
C1 - Football (If Jason was found in the range A1:A3 the output would be Football)
C2 - Soccer (If York was found in the range A1:A3 the output would be Soccer)

As you can tell I want to search the entire column A. I also want to search using the entire (index/range, etc.) of Column B. It would not have to be case sensitive. Thanks for your help!

I think the formula you want for C1 is =IF(SUMPRODUCT(--(NOT(ISERROR(SEARCH(B1,A1:A3))))),"Football") changed to B2 and "Soccer" for C2.

Andrew -- that looks similiar to something in another post. My only problem.. It doesn't seem to pass the fact that if it finds a match using B22... (on A15) for example, that the output is C22.

NBVC -- this seems closer...same sort of problem though. If I pulled this formula down.. the last two variables would be B2 ---C2.... B12 ---C12...etc. I have to pass the corresponding value in Column C when there is a matching using the key from Column 2.


The data I'm using this on is really complex. For example, the field sizes can be in the hundreds.. and I would have to find a Code, Number, Name etc. I am gonna have "rules" in the thousands once I make them up based on the data.

Correct! Trying to decipher your formula. What's the purpose of the 9.9999? Also can I just use a column instead of an absolute range? I'm assuming the absolute range will help with the speed.

This last solution seems really close.

There are still some kinks to work out in the data analysis side but here is the beast I'm working with. One of major problems we have until the rules are more formalized is we're using two fields to "search". I'm just trying to get one to work for now. It'll probably end up being a two step processing or incorporate an IF THEN with one field prioritizing the other. Another problem is some of the criteria fields are blanks so that is passing a match.

Column X is the column I'm searching.
Column Y is where the formula is.

ID TI is the rules worksheet. Column B and D are applicable in this worksheet.
The output should match Column Z.

Good point.

I'm not the one making the rules. The COFIROASTERS rule for MASSIMO might have came from more of the data. I had to reduce the file for it to be under 1MB. He might have just outlooked it early on. These were made really briefly Friday before we left to give me something to start with this morning.

I don't get the current formula to pick up NIVERSAL FURNITURE in Y11 though.

This seems to work great considering only half the rules are probably even in the ID TI table.

I still need to look over some of the logic to fully understand it. Mostly the lookup value part and the 255 part of the INDEX NAME. I'll add to this post if I have any further issues as I move along.

Thanks for all your help.

Hello,

I'd appreciate some help. In my spreadsheet, Column L consists of cells containing a variety of phrases, I need column M to show a number according to the phrase in the corresponding L cell. Column M looks like this

Column M
TRF
OVERTIME
REGULAR
NIGHT SHIFT
PREMIUM PAY

There are thousands of entries so the phrases are repeated and there are a few other phrases. I need to insert a formula that says, if M2 = overtime, 5, if M2=night shift, 4 and so on, and the last part of the formula needs to be "if not any of the mentioned phrases",""

Please help me!

I noticed the formula seems to split the text between spaces when searching. Where if you are searching WALMART, it wouldn't pick up TO:WALMART.

I assume this is because its assigning a numerical value to each string delimited by the spaces. Is there any direction you can point me in or help me with that might allow me to search within strings where I would find the match above?

I guess that goes back to your quote, "Where there is a will there are many ways"

We also contemplated maybe looping a find & replace and putting the necessary space or delimiter around key pieces before doing the lookup. Only time will tell what ends up being better. Thanks again.