VLookup with concatenate not producing result

=VLOOKUP(AB2,Database!B2:N17,CONCATENATE( 10& " " 11& " " &13),FALSE)

Trying to get columns 10, 11 & 13 to join so it says "Mr Noddy Bigshoes" (Salutation, Forename & Surname).

Can't get it to work though, has anyone got any suggestions please?

=VLOOKUP(AB2,Database!B2:N17,CONCATENATE( 10, " ", 11, " ", 13), FALSE)

and this gives me a #Value error???

Can't do that.

You'd have to do
=VLOOKUP(..,..,10,FALSE)&" "&VLOOKUP(..,..,11,FALSE)&" "&VLOOKUP(..,..,13,FALSE)

However, it would probably be better to do the concatenating on the Database Sheet.

So on Databse, say in O2 and filled down put
=K2&" "&L2&" "&N2

Then your formula is
=VLOOKUP(AB2,Database!B2:O17,14, FALSE)

Why can't I combine the results?

Am I meant to amend anything on yours as Excel doesn't like it at all

Originally Posted by Sc0tt1e

Why can't I combine the results?

Good question, I have no idea. But you just can't. Sorry

Originally Posted by Sc0tt1e

Am I meant to amend anything on yours as Excel doesn't like it at all

Yes, I didn't bother writing the entire vlookup 3 times changing only 10 to 11 to 13
so the ..,.. is meant to be the LookupValue and the LookupRange AB2,Database!B2:N17

Looks like Ben did it for you.

VLOOKUP can't return multiple cells. So you're basically giving the formula nonsense instructions for which row it's supposed to return from.

Try this:

=VLOOKUP(AB2,Database!B2:N17,10,FALSE)&" "&VLOOKUP(AB2,Database!B2:N17,11,FALSE)&" "&VLOOKUP(AB2,Database!B2:N17,12,FALSE)

EDIT:
ah, ninja'd by Jonmo1.

Ah, thank you Mr Hensel

I did not know you couldn't combine results, how frustrating.