INDEX MATCH CONCATENATE

[Bay Area DebG]

OS=MSWin2000
App=MS Excel 2003

Within a single workbook, this is what I want to accomplish:

1) Examine the value in a specific cell of my main worksheet (e.g., AB2
{text values})
2) Look up the matching value in "refsheet" (Column H {title="costcenter"})
3) Concatenate other values in "refsheet" (Columns B & C {values are
text=FirstName, LastName})
4) Place value from #3 into a specific cell of my main worksheet (e.g., B2
{Result=FirstName, LastName})

This is the formula that I created to return only the cost center number and
the department name and it works fine:
=INDEX(refsheet!$A$1:$T$26785,MATCH(AB2,refsheet!$A$1:$A$26785,),MATCH("costcenter",refsheet!$A$1:$T$1,))

I played with this quite a bit and this is what I came up with for a
solution, but I only get a "#VALUE!":
=INDEX(refsheet!$A$1:$T$26785,MATCH(AB2,refsheet!$A$1:$A$26785,),CONCATENATE(refsheet!B2,refsheet!C2))

I would appreciate any help.

Thanks,
Bay Area DebG

 

[Martin Fishlock]

Hi Deb

You need to do seperate matches for each cell and then a join using the &.

I also added a comma adjust as required.

=INDEX(refsheet!$B$1:$B$26785,MATCH($AB$2,refsheet!$H$1:$H$26785,0))&","&INDEX(refsheet!$C$1:$C$26785,MATCH($AB$2,refsheet!$H$1:$H$26785,0))