LookUp ?? Match ??

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

I have the following table:

ColA ColB

BOX005 Davies
BOX017 Anderson
BOX019 Jefferies
BOX050 Moores

I would like to have a formula in ColB below that will
look at the last 3 characters in the above table and
compare to the first 3 charaters in ColA below. If there
is a match, return the value in ColB of the above table.

ColA ColB

050S0B1
501S0B1
549S0B1
690S0B1
910S0B1

Thank you in advance.
 
=INDEX(B$1:B$4,MATCH(--LEFT(E1,3),--(RIGHT(A$1:A$4,3)),0))

entered with CTRL SHIFT and ENTER.

Lookup value (col A in 2nd list) in E1.
 
Hi Carl,

Place this formula in column B and copy down:

=INDEX($A$1:$B$4,MATCH(LEFT(A8,3),RIGHT($A$1:$A$4,3),0),2)

To be entered using CTRL+SHIFT+ENTER

Hope this helps!
 
Back
Top