CODEWITHSUNDEEP
excel
user4333011
user4333011

Reputation:

Index Match Not Working on Database Connection

I have excel pulling information from an ODBC database in one worksheet "Data" and am attempting to pull data from it using INDEX MATCH on another worksheet "Price Book Data". The INDEX MATCH formula I made is not working when I type the numbers into the non-data-connection worksheet, however if I cut and paste the exact cell I am attempting to Match from the data-connection worksheet the formula works correctly.

I tried re-formatting the cell in the non-data-connection worksheet to both text and number and neither worked for a successful match. I tested for the vartype with vba and the non-data-connection vartype is returning "5" ("Double-precision floating-point number") and the data-connection worksheet is returning "8" ("String" - both cells are UPCs). My guess is that INDEX MATCH is not working because of that discrepancy, but I changed the non-data-connection cell to both number AND text and neither worked and don't know how to proceed.

I also got the non-vba cell formatting and made sure both cells matched "G" ("General" formatting), even so it still doesn't work.

The question is, how do I reformat the non-data-connection cell appropriately so that the INDEX MATCH works?

Upvotes: 0

Views: 319

Answers (1)

ian0411
ian0411

Reputation: 4265

When you copy data into Excel, sometimes numbers would be treated as text and that is why I used VALUE() syntax to convert text to numbers. Try this array formula (click Ctrl + Shift + Enter together inside formula bar):

=INDEX(Data!B:B,MATCH('Price Book Data'!A2,VALUE(Data!A:A),0))

This should look like the pic below:

Upvotes: 0

Related Questions