I have two s/sheets; one has many rows of data and I shall call thi
datasheet. The other is lookup table containing a column with 30 or s
values. Here is my problem:

I would like to be able to define a range consisting of the values i
one of the columns from the datasheet (which I can manage) and the
perform some kind of lookup/match/index to the lookup table. So what'
the problem? Seems straightforward enough?
The problem is that the values in the lookup table are partial values
For example, a value in the datasheet will be 'XPBARC' but th
'matching' value in the lookup table will just be 'XPBA'! Now, courtes
of Mr. Walkenbach, I have a user-defined function called ISLIKE whic
some of you may be familiar with? But although this works on comparin
two cell values I would prefer not to create a couple of loopin
structures to examine the values in each s/sheet (I also can't do i
anyway! ;-)) but would prefer, if possible, to use a lookup, o
similar, which should be quicker also?

So, if anybody could help I would be extremely grateful indeed!

Many thanks,

Adria

--
Message posted from http://www.ExcelForum.com

Adria

Try the match function. If you sort the data in descending order then use MATCH with the -1 match type, you should get what you are chasing

Say you have the data XPBARE, XPBARD, XPBARC, X in the range A20:A23. The formula =MATCH("XPBA",A20:A23,-1) will come up with 3 which is the position of XPBARC. You can then use this with the INDEX function to find the associated required value

Ton

----- Kobayashi wrote: ----

I have two s/sheets; one has many rows of data and I shall call thi
datasheet. The other is lookup table containing a column with 30 or s
values. Here is my problem

I would like to be able to define a range consisting of the values i
one of the columns from the datasheet (which I can manage) and the
perform some kind of lookup/match/index to the lookup table. So what'
the problem? Seems straightforward enough
The problem is that the values in the lookup table are partial values
For example, a value in the datasheet will be 'XPBARC' but th
'matching' value in the lookup table will just be 'XPBA'! Now, courtes
of Mr. Walkenbach, I have a user-defined function called ISLIKE whic
some of you may be familiar with? But although this works on comparin
two cell values I would prefer not to create a couple of loopin
structures to examine the values in each s/sheet (I also can't do i
anyway! ;-)) but would prefer, if possible, to use a lookup, o
similar, which should be quicker also

So, if anybody could help I would be extremely grateful indeed

Many thanks

Adria


--
Message posted from http://www.ExcelForum.com

Tony,

Many thanks for your help! However, I wish to define and use ranges.
also wish to use this funciton in code, so:

Can I put a range in the below formula to replace the "XPBA" part? O
will I have to use some kind of loop through each of the lookup cells?

=MATCH("XPBA",A20:A23,-1)

Currently I have:

With myRange
For i = 4 To .Rows.Count
If ISLIKE(.Cells(i, 4) & "*", rngB(1, 1)) = True Then
MsgBox "true"
Else: MsgBox "false" '.Cells(i, 4).Select
End If
Next i
End With

This seems to kind of work except that I have to add the (1,1) to th
range which kind of defeats the purpose. Should I scap this altogethe
(if I'm going in the wrong direction?) of should I replace the ISLIK
function with the Match function you have kindly provided?

Regards,

Adria

--
Message posted from http://www.ExcelForum.com