Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning Values from a Table
I have a spreadsheet that has payroll totals by each employee and by
department. I created a table cross-referencing the department code to the position, supervisor, and location. I wanted to be able to run the spreadsheet each month, correlate it to the table and have the information from the table populate based on the department code. I tried to do this with VLOOKUP but the departments can be numerical or alphanumeric. The numeric with 6 digits work and the alphanumeric work. However, when the numeric go to eight digits, it pulls the description from the highest six digit department. For example 55102101 pull from 714043. I've tried changing the formats between numeric and text but can't get it to work. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning Values from a Table
What does your VLOOKUP formula look like? Post an example.
Excel doesn't care if your lookup value is text or numeric. Also the number of digits in the lookup value should have no bearing upon the outcome. I would use =VLOOKUP(value,table,column,FALSE) Some forms of VLOOKUP formulas will return a "closest match" Gord Dibben MS Excel MVP On Sun, 26 Nov 2006 17:05:02 -0800, snmcpa wrote: I have a spreadsheet that has payroll totals by each employee and by department. I created a table cross-referencing the department code to the position, supervisor, and location. I wanted to be able to run the spreadsheet each month, correlate it to the table and have the information from the table populate based on the department code. I tried to do this with VLOOKUP but the departments can be numerical or alphanumeric. The numeric with 6 digits work and the alphanumeric work. However, when the numeric go to eight digits, it pulls the description from the highest six digit department. For example 55102101 pull from 714043. I've tried changing the formats between numeric and text but can't get it to work. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning Values from a Table
I should have also said that now for the alphanumerics, I am getting N/A.
"Gord Dibben" wrote: What does your VLOOKUP formula look like? Post an example. Excel doesn't care if your lookup value is text or numeric. Also the number of digits in the lookup value should have no bearing upon the outcome. I would use =VLOOKUP(value,table,column,FALSE) Some forms of VLOOKUP formulas will return a "closest match" Gord Dibben MS Excel MVP On Sun, 26 Nov 2006 17:05:02 -0800, snmcpa wrote: I have a spreadsheet that has payroll totals by each employee and by department. I created a table cross-referencing the department code to the position, supervisor, and location. I wanted to be able to run the spreadsheet each month, correlate it to the table and have the information from the table populate based on the department code. I tried to do this with VLOOKUP but the departments can be numerical or alphanumeric. The numeric with 6 digits work and the alphanumeric work. However, when the numeric go to eight digits, it pulls the description from the highest six digit department. For example 55102101 pull from 714043. I've tried changing the formats between numeric and text but can't get it to work. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning Values from a Table
I was not putting the false portion in.
VLOOKUP(value, table, column) When I add the false statement, the all numerics work but not the alphanumerics. "Gord Dibben" wrote: What does your VLOOKUP formula look like? Post an example. Excel doesn't care if your lookup value is text or numeric. Also the number of digits in the lookup value should have no bearing upon the outcome. I would use =VLOOKUP(value,table,column,FALSE) Some forms of VLOOKUP formulas will return a "closest match" Gord Dibben MS Excel MVP On Sun, 26 Nov 2006 17:05:02 -0800, snmcpa wrote: I have a spreadsheet that has payroll totals by each employee and by department. I created a table cross-referencing the department code to the position, supervisor, and location. I wanted to be able to run the spreadsheet each month, correlate it to the table and have the information from the table populate based on the department code. I tried to do this with VLOOKUP but the departments can be numerical or alphanumeric. The numeric with 6 digits work and the alphanumeric work. However, when the numeric go to eight digits, it pulls the description from the highest six digit department. For example 55102101 pull from 714043. I've tried changing the formats between numeric and text but can't get it to work. Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning Values from a Table
Figured it out. I had a space at the end of the alphanumerics in the
spreadsheet and not in the table. Looks like it works now. I am checking. Thanks "Gord Dibben" wrote: What does your VLOOKUP formula look like? Post an example. Excel doesn't care if your lookup value is text or numeric. Also the number of digits in the lookup value should have no bearing upon the outcome. I would use =VLOOKUP(value,table,column,FALSE) Some forms of VLOOKUP formulas will return a "closest match" Gord Dibben MS Excel MVP On Sun, 26 Nov 2006 17:05:02 -0800, snmcpa wrote: I have a spreadsheet that has payroll totals by each employee and by department. I created a table cross-referencing the department code to the position, supervisor, and location. I wanted to be able to run the spreadsheet each month, correlate it to the table and have the information from the table populate based on the department code. I tried to do this with VLOOKUP but the departments can be numerical or alphanumeric. The numeric with 6 digits work and the alphanumeric work. However, when the numeric go to eight digits, it pulls the description from the highest six digit department. For example 55102101 pull from 714043. I've tried changing the formats between numeric and text but can't get it to work. Any ideas? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning Values from a Table
That was to be my next suggestion....check for extraneous spaces.
You will probably get good results now. Gord On Sun, 26 Nov 2006 17:29:01 -0800, snmcpa wrote: Figured it out. I had a space at the end of the alphanumerics in the spreadsheet and not in the table. Looks like it works now. I am checking. Thanks "Gord Dibben" wrote: What does your VLOOKUP formula look like? Post an example. Excel doesn't care if your lookup value is text or numeric. Also the number of digits in the lookup value should have no bearing upon the outcome. I would use =VLOOKUP(value,table,column,FALSE) Some forms of VLOOKUP formulas will return a "closest match" Gord Dibben MS Excel MVP On Sun, 26 Nov 2006 17:05:02 -0800, snmcpa wrote: I have a spreadsheet that has payroll totals by each employee and by department. I created a table cross-referencing the department code to the position, supervisor, and location. I wanted to be able to run the spreadsheet each month, correlate it to the table and have the information from the table populate based on the department code. I tried to do this with VLOOKUP but the departments can be numerical or alphanumeric. The numeric with 6 digits work and the alphanumeric work. However, when the numeric go to eight digits, it pulls the description from the highest six digit department. For example 55102101 pull from 714043. I've tried changing the formats between numeric and text but can't get it to work. Any ideas? Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Values and only returning one... | Excel Discussion (Misc queries) | |||
Duplicate values in Pivot table Page Field dropdown.. | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
How do I increase a table values by 1.2 for example? | Excel Worksheet Functions | |||
Returning all values from a lookup - not just the first/last one | Excel Worksheet Functions |