VLOOKUP

[carl]

Is it possible to use the vlookup function to look at the
last 3 values of the cell in the lookup table ?

For example. If my look up table has:

BOX050
BOX223
BOX123

Can the vlookup find the mtch based on just the last 3
values>

Thank you in advance.

 

[Frank Kabel]

Hi
use the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(B1:B100,MATCH(223,RIGHT(A1:A100,3),0))

 

[Guest]

Thanks. Not sure this is what I need. I'll be more precise.

I have a table named "Address" that looks like this:
ColA ColB
BOX050 Anderson
BOX123 Taylor
BOX223 Davies

Then I have another sheet that has the following data
(A1:A3)
ColA
050
123
223

I was hoping to use a formula in ColB that looks at the
data, finds it's match on the table "Address" and returns
the value in ColB of the Table.

 

[Frank Kabel]

Hi
use in B1 on your second sheet the following formula
=INDEX('Address'!$B$1:$B$100,MATCH(A1,RIGHT('Address'!$A$1:$A$100,3),0)
)
and enter this as array formula with CTRL+SHIFT+ENTER

 

[Ken Wright]

The other option is to simply use VLOOKUP as normal, but append "BOX" to the
lookup value, eg:-

=VLOOKUP("BOX"&A1,Address,2,0)

 

[Frank Kabel]

Hi
better than mine

 

[Ken Wright]

No guarantee that the 3 characters are consistent as in the example given
though, but worth mentioning as another option