EXCEL MATCH() function referencing ACCESS table

[abeandip]

I have having a problem with a two dimensional lookup function. The first part works fine but when I use the match function to find the column number I get a #N/A error. Below is the funtion that I used:

=VLOOKUP(($C$28),Table_HRMS_glossary_price_list.accdb,MATCH(W18,Table_HRMS_glossary_price_list.accdb[#Headers],0))

NOTE: Both C28 and W18 are in the "Number" format

Thanks

 

[Andrew Poulsom]

Are both W18 and the header cells real numbers? You can use the ISNUMBER function to check.

 

[abeandip]

The header cells look like this:

cc# | flute | gloss1 | gloss2 | gloss4 | gloss5 | 1500 | 3000 | 10000 | 25000 | 50000 | 75000



[cc#, flute, gloss{1-5}] = "Text" format
[1500 through 75000] = "Number" format with no decimal places or commas

 

[Andrew Poulsom]

Formatting only affects the appearance not the underlying value. What does the ISNUMBER function return when applied to the cell that contains 1500?

 

[abeandip]

=ISNUMBER(Table_HRMS_glossary_price_list.accdb[[#Headers],[1500]])

Returns

FALSE

 

[abeandip]

Could it be a problem with my ACCESS database query?

 

[Andrew Poulsom]

Try:

=VLOOKUP(($C$28),Table_HRMS_glossary_price_list.accdb,MATCH(W18&"",Table_HRMS_glossary_price_list.accdb[#Headers],0))

 

[abeandip]

Works GREAT! What does adding &"" after W18 do for the function?

 

[Andrew Poulsom]

Works GREAT! What does adding &"" after W18 do for the function?

It converts the number into text.