| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP COLUMN VALUE
i have 2 different worksheets.
Sheet 1: A B C D LastName FirstName FirstName LastName Sheet 2: A B UserID FirstName Middle LastName I want to search Sheet2 Column B for containing Sheet1 column D If Sheet1 D is found in Sheet2 B then return Sheet2 A into Sheet1 A Is there a way to do this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP COLUMN VALUE
Yes, lookup Vlookup in Help.
If you still need help, post back with more specific information. -- Regards, Fred "samuel" wrote in message ... i have 2 different worksheets. Sheet 1: A B C D LastName FirstName FirstName LastName Sheet 2: A B UserID FirstName Middle LastName I want to search Sheet2 Column B for containing Sheet1 column D If Sheet1 D is found in Sheet2 B then return Sheet2 A into Sheet1 A Is there a way to do this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP COLUMN VALUE
let me try this
Sheet1 A1 B1 SHOLDER SAMUEL HOLDER BSMITH BARBARA SMITH SHEET 2 A1 B1 C1 SAM SAM HOLDER BARB BARB SMITH I want to find the value of Sheet2:B1 in the B column of Sheet1 and record the value of Sheet1:A1 into Sheet2:c1 The problem is the Sheet1 B column cotains full names. Any thoughts? "Fred Smith" wrote: Yes, lookup Vlookup in Help. If you still need help, post back with more specific information. -- Regards, Fred "samuel" wrote in message ... i have 2 different worksheets. Sheet 1: A B C D LastName FirstName FirstName LastName Sheet 2: A B UserID FirstName Middle LastName I want to search Sheet2 Column B for containing Sheet1 column D If Sheet1 D is found in Sheet2 B then return Sheet2 A into Sheet1 A Is there a way to do this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP COLUMN VALUE
For Sheet2!C1, maybe try:
=INDEX(Sheet1!$A$1:$A$100,MATCH(1,(LEFT(Sheet1!$A$ 1:$A$100,1)=LEFT(A1,1))*(MID(Sheet1!$A$1:$A$100,2, LEN(Sheet1!$A$1:$A$100))=MID(B1,FIND(" ",B1)+1,LEN(B1))),0)) This is an array formula so you should enter it with Ctrl+Shift+Enter. It assumes that your codes consist of the first letter of the first name and the entire last name. It also assumes no variations (e.g. middle names etc). HTH Kostis Vezerides samuel wrote: let me try this Sheet1 A1 B1 SHOLDER SAMUEL HOLDER BSMITH BARBARA SMITH SHEET 2 A1 B1 C1 SAM SAM HOLDER BARB BARB SMITH I want to find the value of Sheet2:B1 in the B column of Sheet1 and record the value of Sheet1:A1 into Sheet2:c1 The problem is the Sheet1 B column cotains full names. Any thoughts? "Fred Smith" wrote: Yes, lookup Vlookup in Help. If you still need help, post back with more specific information. -- Regards, Fred "samuel" wrote in message ... i have 2 different worksheets. Sheet 1: A B C D LastName FirstName FirstName LastName Sheet 2: A B UserID FirstName Middle LastName I want to search Sheet2 Column B for containing Sheet1 column D If Sheet1 D is found in Sheet2 B then return Sheet2 A into Sheet1 A Is there a way to do this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP COLUMN VALUE
thanks for the code. Sheet1 columnB will sometimes contain a middle name.
will that make this impossible? "vezerid" wrote: For Sheet2!C1, maybe try: =INDEX(Sheet1!$A$1:$A$100,MATCH(1,(LEFT(Sheet1!$A$ 1:$A$100,1)=LEFT(A1,1))*(MID(Sheet1!$A$1:$A$100,2, LEN(Sheet1!$A$1:$A$100))=MID(B1,FIND(" ",B1)+1,LEN(B1))),0)) This is an array formula so you should enter it with Ctrl+Shift+Enter. It assumes that your codes consist of the first letter of the first name and the entire last name. It also assumes no variations (e.g. middle names etc). HTH Kostis Vezerides samuel wrote: let me try this Sheet1 A1 B1 SHOLDER SAMUEL HOLDER BSMITH BARBARA SMITH SHEET 2 A1 B1 C1 SAM SAM HOLDER BARB BARB SMITH I want to find the value of Sheet2:B1 in the B column of Sheet1 and record the value of Sheet1:A1 into Sheet2:c1 The problem is the Sheet1 B column cotains full names. Any thoughts? "Fred Smith" wrote: Yes, lookup Vlookup in Help. If you still need help, post back with more specific information. -- Regards, Fred "samuel" wrote in message ... i have 2 different worksheets. Sheet 1: A B C D LastName FirstName FirstName LastName Sheet 2: A B UserID FirstName Middle LastName I want to search Sheet2 Column B for containing Sheet1 column D If Sheet1 D is found in Sheet2 B then return Sheet2 A into Sheet1 A Is there a way to do this? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP COLUMN VALUE
No, it will not make it impossible. My concern now is, what will be the
structure of Sheet2!B1? Will it always be nicknames followed by last name? Is there a case that the nickname is not a prefix of the actual first name? SAM is a prefix of SAMUEL. KOSTIS, as is my name, is not a prefix of KONSTANDINOS, which is my formal name. And if the prefix rule does not hold, do we have any guarantee that the last names are unique? Kostis samuel wrote: thanks for the code. Sheet1 columnB will sometimes contain a middle name. will that make this impossible? "vezerid" wrote: For Sheet2!C1, maybe try: =INDEX(Sheet1!$A$1:$A$100,MATCH(1,(LEFT(Sheet1!$A$ 1:$A$100,1)=LEFT(A1,1))*(MID(Sheet1!$A$1:$A$100,2, LEN(Sheet1!$A$1:$A$100))=MID(B1,FIND(" ",B1)+1,LEN(B1))),0)) This is an array formula so you should enter it with Ctrl+Shift+Enter. It assumes that your codes consist of the first letter of the first name and the entire last name. It also assumes no variations (e.g. middle names etc). HTH Kostis Vezerides samuel wrote: let me try this Sheet1 A1 B1 SHOLDER SAMUEL HOLDER BSMITH BARBARA SMITH SHEET 2 A1 B1 C1 SAM SAM HOLDER BARB BARB SMITH I want to find the value of Sheet2:B1 in the B column of Sheet1 and record the value of Sheet1:A1 into Sheet2:c1 The problem is the Sheet1 B column cotains full names. Any thoughts? "Fred Smith" wrote: Yes, lookup Vlookup in Help. If you still need help, post back with more specific information. -- Regards, Fred "samuel" wrote in message ... i have 2 different worksheets. Sheet 1: A B C D LastName FirstName FirstName LastName Sheet 2: A B UserID FirstName Middle LastName I want to search Sheet2 Column B for containing Sheet1 column D If Sheet1 D is found in Sheet2 B then return Sheet2 A into Sheet1 A Is there a way to do this? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP COLUMN VALUE
Try filling down from C1 in sheet2:
=INDEX(Sheet1!A:A,MATCH(SUBSTITUTE(B1," ","*"),Sheet1!B:B,0)) samuel wrote: let me try this Sheet1 A1 B1 SHOLDER SAMUEL HOLDER BSMITH BARBARA SMITH SHEET 2 A1 B1 C1 SAM SAM HOLDER BARB BARB SMITH I want to find the value of Sheet2:B1 in the B column of Sheet1 and record the value of Sheet1:A1 into Sheet2:c1 The problem is the Sheet1 B column cotains full names. Any thoughts? "Fred Smith" wrote: Yes, lookup Vlookup in Help. If you still need help, post back with more specific information. -- Regards, Fred "samuel" wrote in message ... i have 2 different worksheets. Sheet 1: A B C D LastName FirstName FirstName LastName Sheet 2: A B UserID FirstName Middle LastName I want to search Sheet2 Column B for containing Sheet1 column D If Sheet1 D is found in Sheet2 B then return Sheet2 A into Sheet1 A Is there a way to do this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I combine spreadsheets and documents in one file? | Excel Discussion (Misc queries) | |||
| macro | Excel Discussion (Misc queries) | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Linear Mode
