Find in Vlookup

[atters@gmail.com]

Hi there,
I need to create a function that can perform a vlookup on 'dirty' data.
its almost as if I need to comine the FIND and VLOOKUP functions.
The cell I am doing the lookup on will have data like 'S*234CAT-Woodlands#'.
I need to be able to identify 'CAT' and return the using vlookup Catelog.
Sounds simple, but the same function needs to eveulate 'S*234DOG-Woodlands#' and find DOG and return using vlookup (or simplar) Dogbert.

So I have a normal vlookup table, but my index needs to be cleaned up.
I have attached a sheet to explain a bit better what I am trying to do.
Tim

[DonkeyOte]

Welcome to the Board.

Assuming you want search to be Case Sensitive and you want to return the number associated with first match then:

B4: =INDEX($G$4:$G$7,MATCH(TRUE,INDEX(ISNUMBER(FIND($F$4:$F$7,$A4)),0),0))
copied down

To find number associated with last match (will be first match if only 1 match found)

B4: =LOOKUP(2,1/(ISNUMBER(FIND($F$4:$F$7,$A4))),$G$4:$G$7)
copied down

[atters@gmail.com]

Thanks, works perfectly.