| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Surname and First names formula
welcome, steved !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Steved" wrote: I thank you |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
