VLOOKUP

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

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.
 
Hi
use the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(B1:B100,MATCH(223,RIGHT(A1:A100,3),0))
 
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.
 
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
 
The other option is to simply use VLOOKUP as normal, but append "BOX" to the
lookup value, eg:-

=VLOOKUP("BOX"&A1,Address,2,0)
 
No guarantee that the 3 characters are consistent as in the example given
though, but worth mentioning as another option :-)
 
Back
Top