Look for a list of keywords in a cell and then return the content of the found cell

Hi all users

I have for several times been searching the web for answers on certain Excel challenges that I've had.
I have come across this forum a few times, and now it was the time to sign up for it :-)

And I have a challenge which I hope that you can help me with.
I have a long list (approx. 1500) of cells which consist of text strings.
This would be an example on 7 of the cells:

D.Eng C LC2A-NL30 11/10-F-12-1760
D.Eng C LC2A-NL30 11/10-F-12-1800
D.Eng C LC2A-NL30 13/12-F-12-2000
D.Eng C LC2A-NL30 14/13-F-12-2100
D.Eng C LC2A-NL30 15/14-F-12-2200
D.Eng C LC2A-NL30 15/14-F-12-2350
D.Eng C LC2A-NL30 16/15-F-12-2400

What I need here is a way to search for certain keywords within the list of cells, and if I have a match for all of the keyword, I would like to return the content of the found cell.
I think another example would be good.

Keyword 1: NL
Keyword 2: 2000
Keyword 3: 10

I need to find a cell where keyword 1 and keyword 2 is included.
Furthermore I need the value after the / to be equal or higher than keyword 3.
In this case there is a cell that lives up to all of this (number 3 from the top) and then I would like to return the content of this cell.

In my world this isn't really that simple, so I really hope that you guys can help me.

Thanks in advance

- CLARK

This is what I came up with for your examples. You can change your key words at the top to check different results. I hope this gets you close to what you need. Good luck.

This formula is copied from cell b2 of my worksheet.

=IF(IFERROR(FIND($C$1,A2),0)>0,IF(IFERROR(FIND($E$1,A2),0)>0,IF(IFERROR(INT(MID(A3,FIND("/",A3)+1,2)),0)>$G$1,A2,""),""))

Keyword:3 10 represents which part of the text

Hi Motu040602

Thanks for the fast answer.
Quite a formula you came up with there.

I'm sitting back with a few questions now.
I need to type this formula into only one cell and then look into an area of cells.
So I think we need to add a range of cells into the formula instead of a specific reference to eg. A2.
And this is where I saw the complexity because how are you returning the value of a cell for which you don't know the number on beforehand.
E.g. it came out to be that A123 was containing the keywords I was looking for, then it should be return the content of cell A123. But I don't know that this was the cell I was lookinon beforehand.

What do you think based on this..?

Thanks

Hi nflsales

Keyword 3 should represent the value after the "/".
D.Eng C LC2A-NL30 11/10-F-12-1760

Will every search only yield one answer? I can use an array formula to search out a large range of cells and return the value back, but it is a bit more involved. and will only find the 1st answer it comes to that matches all 3 criteria. I can't work on it right now, but I will give it shot in a couple of hours. Or maybe someone else can pick up sooner. I will check back in little while. Thanks

Hi Moto040602

Yes the search should only yield one answer and it's fine that it only will find the 1st answer that matches all 3 criteria. That is exactly what I need here!
There could be several cells that would match the first two criteria, but I would like to find the first one, which has a value higher than criteria 3. This seems to fits excellent to what you describe.

NB: The criteria 3 could be both two digits and three digits. Just to mention it.
D.Eng C JU4H-NL54 103/94-F-12-2400
D.Eng C JU4H-NL54 114/104-F-12-2600

Thanks for looking into this. I fully appreciate it!!

I think this is what you need. The formula is HUGE. Sorry

=IFERROR(INDEX(A2:A8,MATCH(1,(IF(IFERROR(FIND(C1,A2:A8),0)>0,1,0))*(IF(IFERROR(FIND(E1,A2:A8),0)>0,1,0)*(IF(INT(MID(A2:A8,FIND("/",A2:A8)+1,IF(MID(A2:A8,FIND("/",A2:A8)+3,1)="-",2,3)))>G1,1,0))),0),1),"")


Load your Keywords at the top like before and in the yellow box you should see your result with red letter. Thanks for the challenge! I hope I got it.

One more thing this is an array formula so you may need go into the formula cell and while holding down ctrl + shift hit enter to activate the array formula.

Hi Motu040602

Yes that is quite a big formula, but guess what.... IT WORKS :D
It does exactly what I need it to do, so I can confirm that you got it.

As you already saw coming, I ran into problems with the array formula, but with your small trick I managed to get it working.

Thank you a lot for the help. It was great!!

Excellent! If it isn't to much trouble help me build my rep by hitting the star to the lower left. Thanks!

Consider it done ;-)