My lookup table is like this:

LookUpTable
OLAF0009
OLAF0050
OLAF0325

The data table looks like this:

DataTable
009F0OLA
050F0OLA
325F0OLA

Can a VLOOKUP compare the last 3 characters of the data table with the first
3 characters of the lookup table ?

Thank you in advance.

I would insert a new column A in the table worksheet and extract the first 3
characters of the key column:

=left(b1,3)
and drag down.

Then use:

=vlookup(right(a2,3),sheet2!a:c,3,false)

But you could use an array formula like:
=INDEX(Sheet2!B1:B100,MATCH(RIGHT(A2,3),LEFT(Sheet 2!A1:A100,3),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

carl wrote:

My lookup table is like this:

LookUpTable
OLAF0009
OLAF0050
OLAF0325

The data table looks like this:

DataTable
009F0OLA
050F0OLA
325F0OLA

Can a VLOOKUP compare the last 3 characters of the data table with the first
3 characters of the lookup table ?

Thank you in advance.

--

Dave Peterson