Surname and First names formula

[Guest]

Hello from Steved ( I'm not thinking straight. )

=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$C$2000,0)),VLOOKUP(B4,Staff!$A$2:$C$2000,2,0)&""&VLOOKUP(B4,Staff!$A$2:$C$2000,3,0),"")

I've a sheet Named Staff it has Employee No, Surname and First names.

On Sheet1 I have the above formula and employee No. in Col B.

My objective is to lookup in Staff the employee from the number ie 10022 in B4
Ok now look in Sheet named Staff find 10022 in column A and then copy the
Surname in Col B and The first Names in Col C to Sheet1

What Have I missed in the above formula please or in my state have I got it
completley wrong.

Thankyou.

 

[Biff]

Just change this:

MATCH(B4,Staff!$A$2:$C$2000,0))

To:

MATCH(B4,Staff!$A$2:$A$2000,0))

MATCH only works on a 1 dimensional array (single column or single row)

Biff

 

[Guest]

Try this slightly tweaked version:
=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$A$2000,0)),VLOOKUP(B4,Staff!$A$2:$C$2000,2,0)&" "&VLOOKUP(B4,Staff!$A$2:$C$2000,3,0),"")

Slight tweaks:
a. MATCH range should be a single col: Staff!$A$2:$A$2000
b. Concat between surname & first name now bears a space:" ", re the part:
&" "&

 

[Guest]

Aha, you missed correcting the space between the surname and the first name
<g> !


---

 

[Guest]

I thankyou

Try this slightly tweaked version:
=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$A$2000,0)),VLOOKUP(B4,Staff!$A$2:$C$2000,2,0)&" "&VLOOKUP(B4,Staff!$A$2:$C$2000,3,0),"")

Slight tweaks:
a. MATCH range should be a single col: Staff!$A$2:$A$2000
b. Concat between surname & first name now bears a space:" ", re the part:
&" "&

 

[Guest]

I thankyou.

Just change this:

MATCH(B4,Staff!$A$2:$C$2000,0))

To:

MATCH(B4,Staff!$A$2:$A$2000,0))

MATCH only works on a 1 dimensional array (single column or single row)

Biff

 

[Guest]

welcome, steved !

 

[Biff]

Aha, you missed correcting the space between the surname and the first
name
<g> !

I was preoccupied with trying to figure out how I could stuff about 10
TRIM's in there! <bg>

Biff

 

[Guest]

I was preoccupied with trying to figure out how I could stuff about 10

TRIM's in there! <bg>

Ahh, generally it's good to keep TRIM ... for health reasons! <g>

---