Why MS hasn't implemented a similar UDF in Excel is truly beyond me. No, Power BI isn't the same thing.
PowerQuery is a very powerful tool for ETL is also not comparable as it's not natively integrated into Excel and still acts as an external component. [ex-Excel FTE here)
But you'll probably also love Mesh Spreadsheets: http://chrispsn.com/mesh-preview.html http://mesh-spreadsheet.com/
I literally wrote a "SUPERLOOKUP" formula in VBA that would apply SORT and UNIQUE but making the range dynamic would require an XLL which is a beast I never had time to tame as a hobbyist... this is a dream come true. My eyes are literally watery. Wow.