Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LOOKUP
Hi
So I know that VLOOKUP Uses the first column to search for a value and then looks to columns to its right for the return value. LOOKUP allows for any columns to be used for the search value and any other column for the return value. I have used LOOKUP but am receiving errors with the return values because the Search Value Column is not sorted. I have a file with employee numbers and names and other information that need to be sorted by number and another workbook which is for input but the input is by employee name and I want the employee number to show up automatically. Using LOOKUP I'm getting some wrong values due to the simple fact that the employee names are not sorted alphabetically in the previous workbook. Is there a fix for this? Or a better way to have the employee number automatically inputted when the name is entered? Thanks For your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LOOKUP
I think your question can be answered if you specified your vlookup formula
to only return values which match exactly. Thus, your formula should look something like this: =vlookup(A1, C1:E10, 2, 0) The "0" at the end indicates an exact match is required. " wrote: Hi So I know that VLOOKUP Uses the first column to search for a value and then looks to columns to its right for the return value. LOOKUP allows for any columns to be used for the search value and any other column for the return value. I have used LOOKUP but am receiving errors with the return values because the Search Value Column is not sorted. I have a file with employee numbers and names and other information that need to be sorted by number and another workbook which is for input but the input is by employee name and I want the employee number to show up automatically. Using LOOKUP I'm getting some wrong values due to the simple fact that the employee names are not sorted alphabetically in the previous workbook. Is there a fix for this? Or a better way to have the employee number automatically inputted when the name is entered? Thanks For your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LOOKUP
On Sep 11, 2:14*am, Melissa wrote:
I think your question can be answered if you specified your vlookup formula to only return values which match exactly. Thus, your formula should look something like this: =vlookup(A1, C1:E10, 2, 0) The "0" at the end indicates an exact match is required. " wrote: Hi So I know that VLOOKUP Uses the first column to search for a value and then looks to columns to its right for the return value. LOOKUP allows for any columns to be used for the search value and any other column for the return value. I have used LOOKUP but am receiving errors with the return values because the Search Value Column is not sorted. I have a file with employee numbers and names and other information that need to be sorted by number and another workbook which is for input but the input is by employee name and I want the employee number to show up automatically. Using LOOKUP I'm getting some wrong values due to the simple fact that the employee names are not sorted alphabetically in the previous workbook. Is there a fix for this? Or a better way to have the employee number automatically inputted when the name is entered? Thanks For your help.- Hide quoted text - - Show quoted text - hey melissa thanks for the response the problem i have is that i cannot use VLOOKUP because the names im searching are in column E and the employee numbers are in Column A in one workbook and are sorted by column A. The second workbook is for daily input and the employee should enter their name and automatically have their employee number show up in the column beside it. vlookup doesnt work for me because when im searching their names in the previous workbook to match an employee number it is not in Column A but rather in Column E. Is there any way i can get around this as i cannot reformat the workbooks? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LOOKUP
Oh! I think I know what you mean now: you want to look up a value in Column E
and get the respective value in Column A, i.e. returning a value to the LEFT of the reference! I'm so sorry but I'm stumped by this one. Am curious to see if other people can provide a solution to this. Good luck! " wrote: On Sep 11, 2:14 am, Melissa wrote: I think your question can be answered if you specified your vlookup formula to only return values which match exactly. Thus, your formula should look something like this: =vlookup(A1, C1:E10, 2, 0) The "0" at the end indicates an exact match is required. " wrote: Hi So I know that VLOOKUP Uses the first column to search for a value and then looks to columns to its right for the return value. LOOKUP allows for any columns to be used for the search value and any other column for the return value. I have used LOOKUP but am receiving errors with the return values because the Search Value Column is not sorted. I have a file with employee numbers and names and other information that need to be sorted by number and another workbook which is for input but the input is by employee name and I want the employee number to show up automatically. Using LOOKUP I'm getting some wrong values due to the simple fact that the employee names are not sorted alphabetically in the previous workbook. Is there a fix for this? Or a better way to have the employee number automatically inputted when the name is entered? Thanks For your help.- Hide quoted text - - Show quoted text - hey melissa thanks for the response the problem i have is that i cannot use VLOOKUP because the names im searching are in column E and the employee numbers are in Column A in one workbook and are sorted by column A. The second workbook is for daily input and the employee should enter their name and automatically have their employee number show up in the column beside it. vlookup doesnt work for me because when im searching their names in the previous workbook to match an employee number it is not in Column A but rather in Column E. Is there any way i can get around this as i cannot reformat the workbooks? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LOOKUP
Try this site. It uses a combination of index and match.
http://www.ozgrid.com/Excel/left-lookup.htm " wrote: On Sep 11, 2:14 am, Melissa wrote: I think your question can be answered if you specified your vlookup formula to only return values which match exactly. Thus, your formula should look something like this: =vlookup(A1, C1:E10, 2, 0) The "0" at the end indicates an exact match is required. " wrote: Hi So I know that VLOOKUP Uses the first column to search for a value and then looks to columns to its right for the return value. LOOKUP allows for any columns to be used for the search value and any other column for the return value. I have used LOOKUP but am receiving errors with the return values because the Search Value Column is not sorted. I have a file with employee numbers and names and other information that need to be sorted by number and another workbook which is for input but the input is by employee name and I want the employee number to show up automatically. Using LOOKUP I'm getting some wrong values due to the simple fact that the employee names are not sorted alphabetically in the previous workbook. Is there a fix for this? Or a better way to have the employee number automatically inputted when the name is entered? Thanks For your help.- Hide quoted text - - Show quoted text - hey melissa thanks for the response the problem i have is that i cannot use VLOOKUP because the names im searching are in column E and the employee numbers are in Column A in one workbook and are sorted by column A. The second workbook is for daily input and the employee should enter their name and automatically have their employee number show up in the column beside it. vlookup doesnt work for me because when im searching their names in the previous workbook to match an employee number it is not in Column A but rather in Column E. Is there any way i can get around this as i cannot reformat the workbooks? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LOOKUP
On Sep 11, 2:41*am, Melissa wrote:
Try this site. *It uses a combination of index and match.http://www.ozgrid.com/Excel/left-lookup.htm " wrote: On Sep 11, 2:14 am, Melissa wrote: I think your question can be answered if you specified your vlookup formula to only return values which match exactly. Thus, your formula should look something like this: =vlookup(A1, C1:E10, 2, 0) The "0" at the end indicates an exact match is required. " wrote: Hi So I know that VLOOKUP Uses the first column to search for a value and then looks to columns to its right for the return value. LOOKUP allows for any columns to be used for the search value and any other column for the return value. I have used LOOKUP but am receiving errors with the return values because the Search Value Column is not sorted. I have a file with employee numbers and names and other information that need to be sorted by number and another workbook which is for input but the input is by employee name and I want the employee number to show up automatically. Using LOOKUP I'm getting some wrong values due to the simple fact that the employee names are not sorted alphabetically in the previous workbook. Is there a fix for this? Or a better way to have the employee number automatically inputted when the name is entered? Thanks For your help.- Hide quoted text - - Show quoted text - hey melissa thanks for the response the problem i have is that i cannot use VLOOKUP because the names im searching are in column E and the employee numbers are in Column A in one workbook and are sorted by column A. The second workbook is for daily input and the employee should enter their name and automatically have their employee number show up in the column beside it. vlookup doesnt work for me because when im searching their names in the previous workbook to match an employee number it is not in Column A but rather in Column E. Is there any way i can get around this as i cannot reformat the workbooks?- Hide quoted text - - Show quoted text - thanks for the help melissa that site was a great help i got it working... just if you wanted to know my formula now looks like =INDEX('[Workbook1.xlsx]Sheet1'!$A$3:$G $42,MATCH(B2,'[Workbook1.xlsx]Sheet1'!$E$3:$E$42,0),1) Thanks for the help again it was really helpful |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |