Index Match Not Working on Database Connection

Question

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?

Thanks, ian0411, wrapping the 1st (and 2nd) parameters with VALUE() didn't work in this case. Thank you for the suggestion. I even wrapped the index value just in case and that did not work, either. — user4333011
Excel tossed an error for that exact formula, likely because the match statement was excluded. This is the way I implemented your comment: =INDEX(Data!B2:B3,MATCH(VALUE('Price Book Data'!A2),VALUE(Data!A2:A3),0)) — user4333011
Thanks again, Ian. I'm in the US. I tested the ; and it errored. What do you mean by markup? The reason I ask is because one tab is set up as a database connection. Incidentally I just cut and pasted the data from the database connection (values only) into a new tab and it showed that the number I am attempting to match pasted in "Number stored as text". When I go into the cell and hit enter it re-formats and the INDEX MATCH statement works. I don't want to adjust the "Data" tab at all, though, so how would I adjust the cell in the "Price Book" tab to match that number stored as text? — user4333011
Hi ian, thanks for the response, however the VALUE technique does not appear to be working in this situation. Here is an image of the code I have as a result of your response: ibb.co/enE2Vm — user4333011
Wtf. Awesome! That worked. Please post it as an answer so I can up-vote it and mark as answered. I did type in the {} the first time and it just displayed the formula, so I thought maybe you were doing that to take the screen shot and didn't understand the Shift-Ctrl-Enter thing. I did exactly as you prescribed (selected whole formula and hit shift-ctrl-enter) and it worked. Thank you! — user4333011

ian0411 ian0411 · Accepted Answer · 2017-10-23T21:24:12

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:

Index Match Not Working on Database Connection

1 Answers