Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to input value
Hello,
I ahve a worksheet that returns employee data, the employee is not listed my name but by a letter and number. I want to run a formula that would input the employees name in Column A that is associated with the appropriate employee number in Column B. Would I create a list of all the employee names along with the associated number and run a macro? i.e. Column A Column B Jones H12 Jones H12 Smith H42 Johnson H10 Johnson H10 Johnson H10 Kelly H16 Kelly H16 Thomas H18 Thomas H18 Any help is greatly appreciated! Thanks, Lisa |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to input value
You can use a VLOOKUP function
build a data table (column A must be sorted in ascending order) A B H10 Johnson H12 Jones H42 Smith Lets say you have the employee number in cell C1 The formula is =VLOOKUP(C1,A1:B3,2) This ltakes the value in C1, looks it up in column A and returns the relevant value from column B. You can put your lookup table on a separate sheet by specifying the sheet name in the formula =VLOOKUP(C1,'SheetName'!A1:B3,2) -- HTH JonR Please rate your posts "Gemi" wrote: Hello, I ahve a worksheet that returns employee data, the employee is not listed my name but by a letter and number. I want to run a formula that would input the employees name in Column A that is associated with the appropriate employee number in Column B. Would I create a list of all the employee names along with the associated number and run a macro? i.e. Column A Column B Jones H12 Jones H12 Smith H42 Johnson H10 Johnson H10 Johnson H10 Kelly H16 Kelly H16 Thomas H18 Thomas H18 Any help is greatly appreciated! Thanks, Lisa |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to input value
Jon,
Thank you for your help. Here is were I'm at: I created the data table, a total of 17 employees the table array is A2:B18. I entered the formual in Column C - Employee number begins at D1 and ends at D2377 (the amount of rows will vary with each worksheet). When I go to carry the formula down the entire column is stops at row 18 where the data table ends and it is only returning the employee name in C2 all others are #N/A. The table array is changing in each cell. It starts correctly A2:B18 then goes as such: A3:B19, A4:B20, A5:B21 etc. I would need the table array to remain the same for all the rows and just the lookup value change. Any suggestions? Thank you, Lisa "JonR" wrote: You can use a VLOOKUP function build a data table (column A must be sorted in ascending order) A B H10 Johnson H12 Jones H42 Smith Lets say you have the employee number in cell C1 The formula is =VLOOKUP(C1,A1:B3,2) This ltakes the value in C1, looks it up in column A and returns the relevant value from column B. You can put your lookup table on a separate sheet by specifying the sheet name in the formula =VLOOKUP(C1,'SheetName'!A1:B3,2) -- HTH JonR Please rate your posts "Gemi" wrote: Hello, I ahve a worksheet that returns employee data, the employee is not listed my name but by a letter and number. I want to run a formula that would input the employees name in Column A that is associated with the appropriate employee number in Column B. Would I create a list of all the employee names along with the associated number and run a macro? i.e. Column A Column B Jones H12 Jones H12 Smith H42 Johnson H10 Johnson H10 Johnson H10 Kelly H16 Kelly H16 Thomas H18 Thomas H18 Any help is greatly appreciated! Thanks, Lisa |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to input value
Lisa,
My bad -- I should have told you to use absolute references for your data range =VLOOKUP(C1,$A$1:$B$3,2) The $ character 'locks' the reference to the column or row. This way when you drag the formula down you will continue to refer to the correct range of cells. =VLOOKUP(C1,$A$1:$B$3,2) =VLOOKUP(D1,$A$1:$B$3,2) =VLOOKUP(E1,$A$1:$B$3,2) -- HTH JonR Please rate your posts "Gemi" wrote: Jon, Thank you for your help. Here is were I'm at: I created the data table, a total of 17 employees the table array is A2:B18. I entered the formual in Column C - Employee number begins at D1 and ends at D2377 (the amount of rows will vary with each worksheet). When I go to carry the formula down the entire column is stops at row 18 where the data table ends and it is only returning the employee name in C2 all others are #N/A. The table array is changing in each cell. It starts correctly A2:B18 then goes as such: A3:B19, A4:B20, A5:B21 etc. I would need the table array to remain the same for all the rows and just the lookup value change. Any suggestions? Thank you, Lisa "JonR" wrote: You can use a VLOOKUP function build a data table (column A must be sorted in ascending order) A B H10 Johnson H12 Jones H42 Smith Lets say you have the employee number in cell C1 The formula is =VLOOKUP(C1,A1:B3,2) This ltakes the value in C1, looks it up in column A and returns the relevant value from column B. You can put your lookup table on a separate sheet by specifying the sheet name in the formula =VLOOKUP(C1,'SheetName'!A1:B3,2) -- HTH JonR Please rate your posts "Gemi" wrote: Hello, I ahve a worksheet that returns employee data, the employee is not listed my name but by a letter and number. I want to run a formula that would input the employees name in Column A that is associated with the appropriate employee number in Column B. Would I create a list of all the employee names along with the associated number and run a macro? i.e. Column A Column B Jones H12 Jones H12 Smith H42 Johnson H10 Johnson H10 Johnson H10 Kelly H16 Kelly H16 Thomas H18 Thomas H18 Any help is greatly appreciated! Thanks, Lisa |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to input value
And you don't need the list sorted if you add the FALSE argument.
=VLOOKUP(C1,$A$1:$B$3,2,FALSE) Which will return #N/A is no match found. You may want to mask the error by adding the ISNA function. =IF(ISNA(VLOOKUP(C1,$A$1:$B$3,2,FALSE)),"",VLOOKUP (C1,$A$1:$B$3,2,FALSE)) Gord Dibben MS Excel MVP On Wed, 1 Apr 2009 11:50:14 -0700, JonR wrote: Lisa, My bad -- I should have told you to use absolute references for your data range =VLOOKUP(C1,$A$1:$B$3,2) The $ character 'locks' the reference to the column or row. This way when you drag the formula down you will continue to refer to the correct range of cells. =VLOOKUP(C1,$A$1:$B$3,2) =VLOOKUP(D1,$A$1:$B$3,2) =VLOOKUP(E1,$A$1:$B$3,2) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
input in number form is being multiplied by 1000 when i input. | Excel Discussion (Misc queries) | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
How do I add input data in the input ranges in drop down boxes. | Excel Discussion (Misc queries) | |||
input box = formula bar | Excel Discussion (Misc queries) | |||
=SUMIF(Input!H2:H718,AZ19,Input!E2:E685)AND(IF | Excel Worksheet Functions |