How to do a lookup but only on the last three characters?

[ajnmx]

Had a search, can't find exactly what I need. Any help much
appreciated.

I have a table of data like so, where column A contains a label and B
is some data:

A300 34.5
A320 23.4
A330 48.5

I want to do something like a vlookup, but only on the last three
characters in column A. So something like

=Vlookup("300", A:B, 2, false)

but this of course doesn't work. I know I can do it by inserting a new
column A and doing =Right(B1,3) and using this for the lookup, but I
don't want this, I want to do it in one lookup formula. Is there a way?

 

[Dave Peterson]

=index(b1:b999,match("300",right(a1:a999,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 only use the whole column in xl2007.