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

A

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?
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to lookup based in the first 3 characters of a cell 2
Return a value from one column using an array and a single row 1
Vlookup only the last 6 characters 2
Excel Trying to do a vlookup with a nested right function on the array 3
Lookup help 1
lookup from a table in a pivottable 4
Lookups: Three way match? 5
how to do a double lookup 8

Top