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?

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?

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?

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?

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?

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