I need to look up for a value in a range of cells; the problem is that that
value is going to be part of the value/text (not an exact match) of any cell
in that range. Thus VLOOKUP alone wont work.
i.e
looking for
120206
in a range of cells where one of them contains
120203, 120206, 120201, 120196, 120202, 120208

thanks in advance

Luis,

For example, to pull a value from column C where the string 120206 is in a cell in column A:

=INDEX(C:C,MATCH("*120206*",A:A,FALSE))

The values in column A must be strings - ie, if you entere 120206 into a cell in column A, it should
not be a number.. otherwise, you will need to look for the number first.

HTH,
Bernie
MS Excel MVP


"LuisE" wrote in message
...
I need to look up for a value in a range of cells; the problem is that that
value is going to be part of the value/text (not an exact match) of any cell
in that range. Thus VLOOKUP alone won't work.
i.e
looking for
120206
in a range of cells where one of them contains
120203, 120206, 120201, 120196, 120202, 120208

thanks in advance

You can use wild cards.

=vlookup("*" & "120206" & "*", sheet2!a:b, 2, false)
or
=vlookup("*" & A1 & "*", sheet2!a:b, 2, false)

and probably better:

=if(a1="","",vlookup("*" & A1 & "*", sheet2!a:b, 2, false))

Those wildcards will find a match!

LuisE wrote:

I need to look up for a value in a range of cells; the problem is that that
value is going to be part of the value/text (not an exact match) of any cell
in that range. Thus VLOOKUP alone wont work.
i.e
looking for
120206
in a range of cells where one of them contains
120203, 120206, 120201, 120196, 120202, 120208

thanks in advance

--

Dave Peterson