VLOOKUP and CONCATENATION question

[Rusty315]

Hi all. I was using this formula and it worked perfectly:

=VLOOKUP($D$4&C9,'F''cast & Actual'!$A$6:$O$66,14)

I then tried to enlarge the array like so:

=VLOOKUP($D$4&C9,'F''cast & Actual'!$A$6:$Q$257,14)

Now it returns values that are not in column 14 but elsewhere within the array. I have no idea why though and I'm pulling out what little hair I have left. Can anyone offer any advice? Really appreciate your help!

 

[Canapone]

Hi,

try to add the fourth argument (0 or FALSE)

=VLOOKUP($D$4&C9,'F''cast & Actual'!$A$6:$Q$257,14,0)

=VLOOKUP($D$4&C9,'F''cast & Actual'!$A$6:$Q$257,14,FALSE)

Hope that helps

 

[Aladin Akyurek]

Hi all. I was using this formula and it worked perfectly:

=VLOOKUP($D$4&C9,'F''cast & Actual'!$A$6:$O$66,14)

I then tried to enlarge the array like so:

=VLOOKUP($D$4&C9,'F''cast & Actual'!$A$6:$Q$257,14)

Now it returns values that are not in column 14 but elsewhere within the array. I have no idea why though and I'm pulling out what little hair I have left. Can anyone offer any advice? Really appreciate your help!

The way you set up these formulas requires that the table area is sorted on its first column in ascending order. Otherwise, you need to caary out an exact match. See Canapone's reply post...

 

[Rusty315]

Excellent, worked perfectly! Thanks for your help, it's much appreciated!!