Indirect function and Array formula

**Manumusashi** · 04-28-2011, 07:22 AM

Hi guys,

Does anyone know why the following formula doesn't work as an array formula?:

VLOOKUP(D15:D195,B15:B195&"_client",2,FALSE)

I highlight cells S15:S195, enter the above formula in the formula pane, and press Ctrl+Shift+Enter and I get a #N/A error.

Thanks

Manumusashi

**martindwilson** · 04-28-2011, 07:30 AM

i have no idea why it would work as any formula what are you trying to do?

**daddylonglegs** · 04-28-2011, 07:34 AM

VLOOKUP doesn't take a range or array as the first argument, try this version in S15 copied down

=VLOOKUP(substitute(D15,"-client",""),B$15:C$195,2,FALSE)

[I'm assuming D15 contains an entry like xyz_client, that will match with xyz in column B and return the corresponding value from column C]