vLookup a Concat of a Concat

[robocop1906]

I get a #Value! error with the below formula. Trying to vlookup a concat of a concat.

=VLOOKUP(CONCATENATE(B:B,AI:AI),CONCATENATE('CIL Forecast'!E:E,'CIL Forecast'!L:L),1,FALSE)

 

[rob_sheeds]

check the 3rd variable ("1"), or maybe send some data through.
Cheers

 

[eric cannelora]

I get a #Value! error with the below formula. Trying to vlookup a concat of a concat.

=VLOOKUP(CONCATENATE(B:B,AI:AI),CONCATENATE('CIL Forecast'!E:E,'CIL Forecast'!L:L),1,FALSE)

try entering it as an array formula: click into the formula bar as if you're going to edit it, and press ctrl+shift+enter

i think that will solve your problem...

 

[T. Valko]

I get a #Value! error with the below formula. Trying to vlookup a concat of a concat.

=VLOOKUP(CONCATENATE(B:B,AI:AI),CONCATENATE('CIL Forecast'!E:E,'CIL Forecast'!L:L),1,FALSE)

CONCATENATE doesn't work on arrays.

You're attempting to concatenate the entire columns and it won't work like that.

Typically, you'd concatenate just 2 cells, 1 from each column like this:

=VLOOKUP(B1&AI1...

Or something like this...

=VLOOKUP(B1&"_"&AI1...

I'm not sure I understand what you're trying to do.

Why are you using VLOOKUP with the column number set to 1?

All that will do is return the lookup value (or an error if it's not found).

Are you just wanting to know if the lookup value can be found?

What version of Excel are you using?

 

[eric cannelora]

CONCATENATE doesn't work on arrays.

You're attempting to concatenate the entire columns and it won't work like that.

good point, it should be something like this:

=VLOOKUP(CONCATENATE(B1,AI1),CONCATENATE('CIL Forecast'!E:E,'CIL Forecast'!L:L),1,FALSE)

 

[robocop1906]

Excel 2007, yes just trying to see if it can be found.

 

[eric cannelora]

Excel 2007, yes just trying to see if it can be found.

then either one of these should work (using ctrl+shift+enter):

VLOOKUP(CONCATENATE(B1,AI1),CONCATENATE('CIL Forecast'!E:E,'CIL Forecast'!L:L),1,FALSE)

VLOOKUP(B1&AI1,'CIL Forecast'!E:E&'CIL Forecast'!L:L,1,FALSE)

where b1+ai1 is the thing you're trying to find

 

[T. Valko]

Excel 2007, yes just trying to see if it can be found.

Ok, try something like this...

=IF(COUNTIFS('CIL Forecast'!E:E,B1,'CIL Forecast'!L:L,AI1),"Found","Not Found")