vlookup where the query is a substring

[hog.badger]

I have a huge database of game items from an online game spread across
several sheets, and I'm trying to finda match. The problem is that my
search string is often a substring of the main alpha field in the
excel database.

Using Excel 2000, there doesn't seem to be a built-in command for
doing this.

How would I do this via a macro .... as I assume a macro can be made
to do almost anything?

.... or maybe SQL ... if Excel 2k supports SQL queries within a
spreadsheet ... of which I am totally ignorant.

Any help appreciated.

 

[Bernie Deitrick]

For example, to find the substring "war"

=VLOOKUP("*war*",A,4,False)

Note that this will only return the value for the first instance, so choose your sub-strings well...

HTH,
Bernie
MS Excel MVP

 

[hog.badger]

VLOOKUP supports wild-cards? ... wow, suddenly everything becomes
simpler.

If I were to transfer my database to an Access database, which I have
started to do, would this be any more useful for the purposes of this
query?

Thanks,
H

 

[Bernie Deitrick]

H,

A database is easier to query to get the multiples: war quest, total war, war of the roses would all
re returned by *war*. However, you could use data filters in Excel to achieve the same end. Apply
the filters, use the custom filter of 'includes' / war, and all those would be shown in your
filtered list. Personally, until the list gets too big, I like Excel, but then I almost always am
doing calcs based on the data in the database....

HTH,
Bernie
MS Excel MVP