Surname and First names formula
 

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

=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$C$2000,0)),VLOOK UP(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.

Surname and First names formula
 

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

"Steved" wrote in message
...
Hello from Steved ( I'm not thinking straight. )

=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$C$2000,0)),VLOOK UP(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.

Surname and First names formula
 

Try this slightly tweaked version:
=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$A$2000,0)),VLOOK UP(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:
&" "&
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steved" wrote:
Hello from Steved ( I'm not thinking straight. )

=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$C$2000,0)),VLOOK UP(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.

Surname and First names formula
 

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Surname and First names formula
 

I thankyou

"Max" wrote:

Try this slightly tweaked version:
=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$A$2000,0)),VLOOK UP(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:
&" "&
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steved" wrote:
Hello from Steved ( I'm not thinking straight. )

=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$C$2000,0)),VLOOK UP(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.

Surname and First names formula
 

I thankyou.

"Biff" wrote:

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

"Steved" wrote in message
...
Hello from Steved ( I'm not thinking straight. )

=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$C$2000,0)),VLOOK UP(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.

Surname and First names formula
 

welcome, steved !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steved" wrote:
I thank you

Surname and First names formula
 

"Max" wrote...
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

Surname and First names formula
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---