INDEX MATCH not working with database connection

[bpbd]

Hi!

I'm working on a workbook that has one tab "Price Book" that I would like to pull data into and a tab called "Data" that has a connection that pulls information in from a database.

I want to use index match on the "Price Book" tab to pull data from the "Data" tab, however it is not working and returns "#n/a". I've tested the formula and it works as it is, but it appears as if it may be returning "#n/a" because of a difference in formatting or data type between the cell I am trying to match and the database list I am trying to match from(?). Here is the formula:

=INDEX(Data!B2:B3,MATCH('Price Book'!A2,Data!A2:A3,0))

I tested this by copying and pasting the exact cell from the "Data" tab into the match cell in the "Price Book" tab and it matched correctly, so the formula itself appears to be workging. Further investigation showed the database was pulling in some trailing white-space so I use both TRIM and CLEAN and neither of them worked. I also tested for the cell format and initially there was a difference in that the "Price Book" tab cell was returning F0 ("0" number format) and for the cell in "Data" it was returning "G" (General formatting). I changed the formatting of the cell I am attempting to match to General and it still did not work. Investigating even further I found that the VarType of the cell in the "Price Book" tab is returning 5 ("Double-precision floating-point number") and the "Data" tab is returning 8 ("String"), but I have no idea how to get those to match and I've tried to change the "Price Book" tab to Text Formatting thinking that would change it to VarType 8 but it did not (without VBA, which I'd prefer to not use just to get an INDEX MATCH to work), and am not even sure if that's the problem.

I just want INDEX MATCH to work and have no idea where to go from here and have found no information from others who have had the same issue. Does anyone have any leads or information on how I could solve this issue and return the actual value instead of "#n/a"?

Thanks for hosting, Mr. Excel!

Best,
BD

 

[bpbd]

I just cut and pasted the data from the "Data" database tab "values only" into a new tab and the number I am trying to match pasted in as "Number stored as text". After I go into the cell and hit enter it then adjusts to the E+ notation and the match works fine. But I don't want to change all the data in the "Data" worksheet like that, I would rather change the term I am trying to match so that the formula works.

 

[bpbd]

Someone on StackOverflow helped me solve this problem. I needed to create an array formula from this by selecting the entire formula in the formula bar and hitting Ctrl-Shift-Enter. That solved this issue. Thanks, StackOverflow!