Excel search query

[semi-pro waster]

Afternoon, I'm trying to find if particular text strings are present in a long list of data i.e. to look up based on one cell and find out if the text is anywhere within a list of cells - I don't even know if it is possible but if it is then it appears to be slightly beyond my Excel skills or maybe just my brain capacity today.

To explain a bit further I've got lists of reference numbers say in cell F1 I have "D12345D" and I'd like to see if they appear in another list (say E3:E768) where they may be in any position in the text e.g. in cell E21 "This is D12345D here" or in E453 "At the end of this is D12345D". I can use Search or Find to check if it is there but they only appear to work where it is on the same row, it seems as if I'd need to combine them with a vLookup in some way but I can't seem to think of anything at the moment which will solve the problem. All I need is a true/false type response to check whether that reference has appeared before at any point in the list.

Thanks for any help.

 

[CreAtiv3]

Isnt a vlookup exactly what you want?

You have a list of reference numbers in F and you want to see if they are in E?
=vlookup(F1,($E$3:$E$768),1,FALSE)

If there is more than just the reference number in column E could you not remove everything but the reference for the purpose of matching them together?

Upload a sample of the data if you can, perhaps i've misunderstood completely!

 

[Participant]

VLOOKUP would only work if the string matched the cell.

Try this:

This matches the string but searches within any amount of text, the '*' being a wildcard like in windows searching. Match type 0 searches an exact match and doesn't require that the data be sorted.

 

[CreAtiv3]

Aha I understand the question now. You need to see what cell in column E contains the string in F1. Makes sense after having lunch

Robbies should do the job nicely.

For added error checking you could expand on it with the following:

If you wanted to know what the contents of the cell was rather than the row number use:

What this wont cover is where there are multiple instances of the string, it will only return the first result it finds...

 

[semi-pro waster]

Cheers very much gents, Robbie's suggestion worked fine so I'm due you a pint I think although I adapted it slightly further to disguise where there was no data entered. I did have a play about with the Match function before when looking for a solution but didn't do anything with the wildcard feature and that was what made it possible.

Thanks for the error checking as well CreAtiv3, I've not used it in this instance but I may try it if I ever need further iterations, that it doesn't pick up multiple instances isn't a problem as I just needed to know if it appeared at all to trigger further investigation.