Excel Lookup Question

[tebbe]

I am looking for the right formula/function to use for the followin
data lookup scenario.

I have a 2-column spreasheeet (spreadsheet ONE) with the first colum
containing a unique record number and second column containing fre
flowing text in each cell. Within column B cells, there is referenc
text (alpha-numberic server host names) that I want to find and brin
back the unique record number (column A) every time it finds a match.
have a second spreadhseet (spreadshett TWO) with a list of all th
reference text (alpha-numberic server host names) that I want to appl
a formula to go find ALL the records in spreadhseet ONE that have tha
text within column B.

I have used VLOOKUP often but not sure of a way to have it match on
text string within a larger text string and also it stops at the firs
match anyway as far as I know.

There must be a simple way to perform this query but I'm a bit of
novice at this

 

[Guest]

One big problem is how are you going to return ALL of the matches for a given record into one cell? Excel usually just returns one result per cell. Have you thought about how you want the answer to be presented?

If you have less than 250ish records, you could make a huge matrix, and then concatenate the results, but if not you could be looking at writing some kind of VBA function. Id so there are people better qualified than me to help.