Type errors when using VLOOKUP

When using VLOOKUP it will only find matches of the same data type as source.

The link below has some solutions;

https://exceljet.net/formula/vlookup-with-numbers-and-text

TL;DR,

=IFERROR(VLOOKUP(id,planets,3,0),VLOOKUP(id&"",planets,3,0))

Use OLE DB (Excel Spreadsheet) in SSRS

I have been learning a lot of SSRS and SQL server recently and have wondered how to apply the awesome toolset of SSRS to some other problems I have found.

From most of the resources I have found it is usually quoted that the only way to use excel as a datasource for SSRS is by adding the excel document as a ODBC, however in lots of environments that will not be possible due to security settings. 

In the back of my mind I was sure that I had seen excel used with OLE DB data source before. 

Finally I found an example of it working here;

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1b52b758-1787-4aa2-a760-f98090667788/excel-data-source-for-report-without-using-odbc?forum=sqlreportingservices

Essentially you use the following connection string

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

and all works as hoped 

Success!

The only other point to make is that you need to reference the sheet name with a $ suffix,

SELECT
F1 AS rowNum
,F2 AS theValue
FROM
[Data3$]

More resources: 

I wish I found these earlier! 

Connection strings,
https://www.connectionstrings.com/excel-2007/

Stack exchange question about the same problem:
http://stackoverflow.com/questions/24499299/connecting-excel-2013-to-ssrs-2012-to-use-as-a-datasource