Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Look-up Function!
Hi al - PLEASE HELP
Here is the situation - I have names of people that attended a design session in a excel spreadsheet, listed verticaly. I have a seperate excel spreadsheet with the "time approver" for each employee in the hospital. I need to match the attendee with the "time approver". There are about 2000 attendees and I need to look up their names from about 15,000 hospital employees. The attendee list does not have middle initials but the "time approver" list has middle initials. None of my look up formulas and/orif formulas are working - can you please help so I dont have manually match names. Thanks so much -- Candace |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Look-up Function!
A couple of questions. First, do you have the complete name (first and last
or first, middle and last) all in one cell, or is each name part in its own column? Is the only thing you are trying to do is see if the names exist or not? Or is there something you are going to do once you find the name? (By the way, an indication of column designations for names and any other information you will be working with would be useful.) Rick "Candace@UH" wrote in message ... Hi al - PLEASE HELP Here is the situation - I have names of people that attended a design session in a excel spreadsheet, listed verticaly. I have a seperate excel spreadsheet with the "time approver" for each employee in the hospital. I need to match the attendee with the "time approver". There are about 2000 attendees and I need to look up their names from about 15,000 hospital employees. The attendee list does not have middle initials but the "time approver" list has middle initials. None of my look up formulas and/orif formulas are working - can you please help so I dont have manually match names. Thanks so much -- Candace |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Look-up Function!
Thanks for your response:
Sheet 1 has the attendees names appearing in Column A as - Last, First Sheet 2 has Column A with all employees in the company and Column B has the coordinating "approvers" appearing as - Last, First+Middle Initial (as a side note -In sheet 1, I used the text to column function to seperate last and first name) I am trying to do two things: See if the attendees name in Sheet 1 is listed in Sheet 2 If yes, return the name of the Approver Sheet 1 Sheet 2 Attendees Employee Approver Cloud, Mindy Cloud, Mindy L Paper, Moon Sun, Child Sun, Child F Mark, Question Right, Left Right, Left Q Me, Help Thanks again, I hope this detail helps, Im going crazy. -- Candace "Rick Rothstein (MVP - VB)" wrote: A couple of questions. First, do you have the complete name (first and last or first, middle and last) all in one cell, or is each name part in its own column? Is the only thing you are trying to do is see if the names exist or not? Or is there something you are going to do once you find the name? (By the way, an indication of column designations for names and any other information you will be working with would be useful.) Rick "Candace@UH" wrote in message ... Hi al - PLEASE HELP Here is the situation - I have names of people that attended a design session in a excel spreadsheet, listed verticaly. I have a seperate excel spreadsheet with the "time approver" for each employee in the hospital. I need to match the attendee with the "time approver". There are about 2000 attendees and I need to look up their names from about 15,000 hospital employees. The attendee list does not have middle initials but the "time approver" list has middle initials. None of my look up formulas and/orif formulas are working - can you please help so I dont have manually match names. Thanks so much -- Candace |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Look-up Function!
Assuming you have header rows so the names and associated data start on Row
2, try putting this formula in some column on Sheet 1 (starting on Row 2) and copy it down... =IF(ISNUMBER(MATCH(A2&"*",Sheet2!A$2:Sheet2!A$1500 0,0)),INDEX(Sheet2!B$2:Sheet2!B$15000,MATCH(A2&"*" ,Sheet2!A$2:Sheet2!A$15000,0)),"<<Not On List") I wasn't sure what you wanted to do if the name was not on the list, so I flagged it with this... "<<Not On List"... change the message to suit your needs. Does this do what you wanted? Rick "Candace@UH" wrote in message ... Thanks for your response: Sheet 1 has the attendees names appearing in Column A as - Last, First Sheet 2 has Column A with all employees in the company and Column B has the coordinating "approvers" appearing as - Last, First+Middle Initial (as a side note -In sheet 1, I used the text to column function to seperate last and first name) I am trying to do two things: See if the attendees name in Sheet 1 is listed in Sheet 2 If yes, return the name of the Approver Sheet 1 Sheet 2 Attendees Employee Approver Cloud, Mindy Cloud, Mindy L Paper, Moon Sun, Child Sun, Child F Mark, Question Right, Left Right, Left Q Me, Help Thanks again, I hope this detail helps, Im going crazy. -- Candace "Rick Rothstein (MVP - VB)" wrote: A couple of questions. First, do you have the complete name (first and last or first, middle and last) all in one cell, or is each name part in its own column? Is the only thing you are trying to do is see if the names exist or not? Or is there something you are going to do once you find the name? (By the way, an indication of column designations for names and any other information you will be working with would be useful.) Rick "Candace@UH" wrote in message ... Hi al - PLEASE HELP Here is the situation - I have names of people that attended a design session in a excel spreadsheet, listed verticaly. I have a seperate excel spreadsheet with the "time approver" for each employee in the hospital. I need to match the attendee with the "time approver". There are about 2000 attendees and I need to look up their names from about 15,000 hospital employees. The attendee list does not have middle initials but the "time approver" list has middle initials. None of my look up formulas and/orif formulas are working - can you please help so I dont have manually match names. Thanks so much -- Candace |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Look-up Function!
Thanks so much Rick! I used your formula and it worked.
I'm so stress free, I am leaving work early!! Thanks again! -- Candace "Rick Rothstein (MVP - VB)" wrote: Assuming you have header rows so the names and associated data start on Row 2, try putting this formula in some column on Sheet 1 (starting on Row 2) and copy it down... =IF(ISNUMBER(MATCH(A2&"*",Sheet2!A$2:Sheet2!A$1500 0,0)),INDEX(Sheet2!B$2:Sheet2!B$15000,MATCH(A2&"*" ,Sheet2!A$2:Sheet2!A$15000,0)),"<<Not On List") I wasn't sure what you wanted to do if the name was not on the list, so I flagged it with this... "<<Not On List"... change the message to suit your needs. Does this do what you wanted? Rick "Candace@UH" wrote in message ... Thanks for your response: Sheet 1 has the attendees names appearing in Column A as - Last, First Sheet 2 has Column A with all employees in the company and Column B has the coordinating "approvers" appearing as - Last, First+Middle Initial (as a side note -In sheet 1, I used the text to column function to seperate last and first name) I am trying to do two things: See if the attendees name in Sheet 1 is listed in Sheet 2 If yes, return the name of the Approver Sheet 1 Sheet 2 Attendees Employee Approver Cloud, Mindy Cloud, Mindy L Paper, Moon Sun, Child Sun, Child F Mark, Question Right, Left Right, Left Q Me, Help Thanks again, I hope this detail helps, Im going crazy. -- Candace "Rick Rothstein (MVP - VB)" wrote: A couple of questions. First, do you have the complete name (first and last or first, middle and last) all in one cell, or is each name part in its own column? Is the only thing you are trying to do is see if the names exist or not? Or is there something you are going to do once you find the name? (By the way, an indication of column designations for names and any other information you will be working with would be useful.) Rick "Candace@UH" wrote in message ... Hi al - PLEASE HELP Here is the situation - I have names of people that attended a design session in a excel spreadsheet, listed verticaly. I have a seperate excel spreadsheet with the "time approver" for each employee in the hospital. I need to match the attendee with the "time approver". There are about 2000 attendees and I need to look up their names from about 15,000 hospital employees. The attendee list does not have middle initials but the "time approver" list has middle initials. None of my look up formulas and/orif formulas are working - can you please help so I dont have manually match names. Thanks so much -- Candace |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Look-up Function!
You are quite welcome; I'm glad I was able to help out with your formula,
the lowering of your stress level and your early quit-time.<g Rick "Candace@UH" wrote in message ... Thanks so much Rick! I used your formula and it worked. I'm so stress free, I am leaving work early!! Thanks again! -- Candace "Rick Rothstein (MVP - VB)" wrote: Assuming you have header rows so the names and associated data start on Row 2, try putting this formula in some column on Sheet 1 (starting on Row 2) and copy it down... =IF(ISNUMBER(MATCH(A2&"*",Sheet2!A$2:Sheet2!A$1500 0,0)),INDEX(Sheet2!B$2:Sheet2!B$15000,MATCH(A2&"*" ,Sheet2!A$2:Sheet2!A$15000,0)),"<<Not On List") I wasn't sure what you wanted to do if the name was not on the list, so I flagged it with this... "<<Not On List"... change the message to suit your needs. Does this do what you wanted? Rick "Candace@UH" wrote in message ... Thanks for your response: Sheet 1 has the attendees names appearing in Column A as - Last, First Sheet 2 has Column A with all employees in the company and Column B has the coordinating "approvers" appearing as - Last, First+Middle Initial (as a side note -In sheet 1, I used the text to column function to seperate last and first name) I am trying to do two things: See if the attendees name in Sheet 1 is listed in Sheet 2 If yes, return the name of the Approver Sheet 1 Sheet 2 Attendees Employee Approver Cloud, Mindy Cloud, Mindy L Paper, Moon Sun, Child Sun, Child F Mark, Question Right, Left Right, Left Q Me, Help Thanks again, I hope this detail helps, Im going crazy. -- Candace "Rick Rothstein (MVP - VB)" wrote: A couple of questions. First, do you have the complete name (first and last or first, middle and last) all in one cell, or is each name part in its own column? Is the only thing you are trying to do is see if the names exist or not? Or is there something you are going to do once you find the name? (By the way, an indication of column designations for names and any other information you will be working with would be useful.) Rick "Candace@UH" wrote in message ... Hi al - PLEASE HELP Here is the situation - I have names of people that attended a design session in a excel spreadsheet, listed verticaly. I have a seperate excel spreadsheet with the "time approver" for each employee in the hospital. I need to match the attendee with the "time approver". There are about 2000 attendees and I need to look up their names from about 15,000 hospital employees. The attendee list does not have middle initials but the "time approver" list has middle initials. None of my look up formulas and/orif formulas are working - can you please help so I dont have manually match names. Thanks so much -- Candace |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |