I have a table which was created using external data (SQL Server) with
Microsoft Query. The table has two columns: Project numbers, and time codes
assigned to the projects.

On a separate worksheet, I am tring to create a data validation list which
looks up a project number typed in a cell and populates the list with project
time codes. I'm and using this formula:
OFFSET(ProjectStart,MATCH(D3,ProjectColumn,0)-1,1,COUNTIF(ProjectColumn,D3)

However, I get #n/a. So I broke the formula down and found that (at least)
the MATCH(D3,ProjectColumn,0) is returning a #n/a. I tried this within a
single cell to test the formula.

I have used table columns as lookup arrays before without trouble. I was
wondering if the issue is with the Query (which I used so the data could be
pre-sorted ascending), or the table, or ??

Any help would be appreciated.

Sounds weird. Maybe your numbers are stored as text:
http://www.dailydoseofexcel.com/arch...tored-as-text/

Take a look at this too:
http://www.contextures.com/xlFunctions03.html


Regards,
Ryan---

--
RyGuy


"MNS" wrote:

I have a table which was created using external data (SQL Server) with
Microsoft Query. The table has two columns: Project numbers, and time codes
assigned to the projects.

On a separate worksheet, I am tring to create a data validation list which
looks up a project number typed in a cell and populates the list with project
time codes. I'm and using this formula:
OFFSET(ProjectStart,MATCH(D3,ProjectColumn,0)-1,1,COUNTIF(ProjectColumn,D3)

However, I get #n/a. So I broke the formula down and found that (at least)
the MATCH(D3,ProjectColumn,0) is returning a #n/a. I tried this within a
single cell to test the formula.

I have used table columns as lookup arrays before without trouble. I was
wondering if the issue is with the Query (which I used so the data could be
pre-sorted ascending), or the table, or ??

Any help would be appreciated.