VLOOKUP returns #REF using Query as source

[Helo]

I created a query to pull aggregated data from my access database. It
returns the following information that is entered into an area below the main
spreadsheet data.

Query results are:

Labor Code Regular Hours
Aircraft Mechanic 64
Aircraft Quality Inspector 91.5
Avionics Mechanic 268
Avionics Mechanic Lead 24
Configuration Management Specialist 32
Material Handler 7
Parts Clerk 5.5
Production Planner 57
Production Planning Supervisor 5
Quality Inspector 9
Quality Supervisor 9
Structural Installer 118
Structural Installer Lead 128
Warehouse Manager 3.5

I then want to find each labor code and copy the hours to the corresponding
cell in the top part of the spreadsheet. I entered VLOOKUP using the dialog
box and the following appeared in the formula bar with #REF in the cell.

=VLOOKUP($A$25,Table_Query_from_RegHrsByLabCat[Labor Code],2)

I changed the phrase Table_Query_from_RegHrsByLabCat[Labor Code] to cell
references (both absolute and relative) and still have the #REF error.

I copied and pasted the values from the query results into different cells,
and I still get the same result.

The help hint says "Invalid Cell Reference."

If I substitute LOOKUP for any of the VLOOKUP functions, the result is #N/A.
Go figure.

It is probably something simple I've overlooked, but I'm going crazy trying
to make this work.

Thanks in advance.

 

[Pete_UK]

There is a 4th (optional) parameter which can be used by VLOOKUP - if
it is set to 0 (or FALSE) then VLOOKUP will search for an exact match
and will return #N/A if there is no matching value. You can trap this
by having:

=IF(ISNA(VLOOKUP( ... ),"not present",VLOOKUP( ... )

Check that A25 does indeed contain an exact match with one of the
entries under Labor Code, and that you do not have any trailing spaces
(or character code 160) in those entries.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, I missed a few brackets:

=IF(ISNA(VLOOKUP( ... )),"not present",VLOOKUP( ... ))

where you would have something like:

VLOOKUP($A$25,table_range,2,0)

Hope this helps.

Pete

There is a 4th (optional) parameter which can be used by VLOOKUP - if
it is set to 0 (or FALSE) then VLOOKUP will search for an exact match
and will return #N/A if there is no matching value. You can trap this
by having:

=IF(ISNA(VLOOKUP( ... ),"not present",VLOOKUP( ... )

Check that A25 does indeed contain an exact match with one of the
entries under Labor Code, and that you do not have any trailing spaces
(or character code 160) in those entries.

Hope this helps.

Pete

I created a query to pull aggregated data from my access database.  It
returns the following information that is entered into an area below the main
spreadsheet data.

Query results are:

Labor Code                                 Regular Hours
Aircraft Mechanic                                  64
Aircraft Quality Inspector                 91.5
Avionics Mechanic                                  268
Avionics Mechanic Lead             24
Configuration Management Specialist      32
Material Handler                                   7
Parts Clerk                                               5.5
Production Planner                                 57
Production Planning Supervisor             5
Quality Inspector                                  9
Quality Supervisor                                 9
Structural Installer                               118
Structural Installer Lead                  128
Warehouse Manager                                  3.5

I then want to find each labor code and copy the hours to the corresponding
cell in the top part of the spreadsheet.  I entered VLOOKUP using thedialog
box and the following appeared in the formula bar with #REF in the cell..

=VLOOKUP($A$25,Table_Query_from_RegHrsByLabCat[Labor Code],2)

I changed the phrase Table_Query_from_RegHrsByLabCat[Labor Code] to cell
references (both absolute and relative) and still have the #REF error.

I copied and pasted the values from the query results into different cells,
and I still get the same result.

The help hint says "Invalid Cell Reference."

If I substitute LOOKUP for any of the VLOOKUP functions, the result is #N/A.
 Go figure.

It is probably something simple I've overlooked, but I'm going crazy trying
to make this work.

Thanks in advance.- Hide quoted text -

- Show quoted text -