VLookup if cell contains (rather than matches) a string.

[Alex H]

Hi folks, I have a question:

If Column A contains any number of 5 letter document numbers as follows:
90521 90876
78431
97863 86452 13576 98761 84521
etc.

And Column B contains the location of those documents which I would like to return:
Location A
Location D
Location E
etc.

How can I return the location of a given document (e.g.97863) in a cell?

A vlookup or index/match would only find my document if it matched what's in column A exactly and I want it if the row contains.
i.e. I can find document 78431 by using =VLOOKUP(78431,$A$1:$B$3,2,FALSE), or =INDEX(B1:B3,MATCH(78431,A1:A3,0),1) but this doesn't work if there is more than one document in the location (e.g. document 97863).

Any suggestions?

[Moo the Dog]

You could try this:

=VLOOKUP("*"&78431&"*",$A$1:$B$3,2,0)

But it will only return the first match it finds.

- Moo

[Alex H]

Just what I needed and so simple! Thank you!

[Moo the Dog]

You're welcome. Glad to help. =)

- Moo