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

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,FAL SE)

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


"Harold Good" wrote:

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

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

===========================
"Ron Coderre" wrote in message
...
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,FAL SE)

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


"Harold Good" wrote:

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

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


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

XL2002, WinXP


"Harold Good" wrote:

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

===========================
"Ron Coderre" wrote in message
...
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,FAL SE)

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


"Harold Good" wrote:

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