combine Vlookup with the Right function

H

Harold Good

Hi,

Is there a way to combine the Right function with Vlookup?

I have a statement that comes each month with lots of donors numbers
identifying their contributions. Here is an example:
501257-287640

The last six digits are unique to individual donors. I'd like to use Vlookup
to identify their contribution each month.

I need a formula that might look something like this, but this is pretty
lame and doesn't work.
=VLOOKUP(287640,RIGHT(Data!I20:p250,6),4,FALSE)

I've tried a few other combinations but to no avail. Is there anyway to
rearrange this combination to make it work? I'd prefer not to have to run a
macro to do it.

Thanks for any advice you can offer.

Harold
 
R

Ron Coderre

Your formula is looking for a number in a range of text values. That makes a
difference to Excel.

To allow for Donor Numbers beginning with zero, I'd search for a text value
in the range of text values.

=VLOOKUP("287640",INDEX(RIGHT(I20:p250,6),0),4,FALSE)

Note: the INDEX function with zero as its 2nd argument causes the formula to
mimick an array formula, even though you commit the formula with ENTER.

This ARRAY FORMULA version (without the INDEX function) requires that you
commit it with CTRL+SHIFT+ENTER.
=VLOOKUP("287640",RIGHT(I20:p250,6),4,FALSE)

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
H

Harold Good

Very impressive Ron, I hadn't realized that I was getting in arrays here.
But I tried both your formulas and they both work, just as you said.

I'd never heard of the Index as an array with that 2nd argument.

Thanks for the lesson! I'm off an running now.
Harold

===========================
 
R

Ron Coderre

I'm glad that worked for you, Harold....Thanks for letting me know.


***********
Regards,
Ron

XL2002, WinXP
 
Top