Surname and First names formula

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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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

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

"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

> 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
---

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

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:
&" "&
--
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)),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.
>

I thankyou

"Max" wrote:

> 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:
> &" "&
> --
> 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)),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.
> >

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