Returning Values from a Table

[Guest]

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]

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

 

[Guest]

I should have also said that now for the alphanumerics, I am getting N/A.

 

[Guest]

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.

 

[Guest]

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]

That was to be my next suggestion....check for extraneous spaces.

You will probably get good results now.

Gord

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 MS Excel MVP