Power Query IndexMatch Lookup with multiple criteria

Folks.
I am building a Power Query model, and need help with coding a lookup with multiple criteria, similar to INDEX MATCH. The Index Mathc function is far too slow in Excel because I have large amounts of data. I suspect doing it in PowerQuery will be considerable faster.
I have two tables called NPDFmTops and OHP_PLotData
I want to return a value in the OHP_PlotData table, that comes from the NPDFmTops, based on 4 different criteria The INDEX/MATCH combo I wish to replicate in PowerQuery is

Formula:

Please Login or Register  to view this content.

In otherwords
return IsuName
When Wellbore=wlbName AND
IsuLevel = "FORMATION" AND
MD RKB > IsuTopDepth AND
MD RKB <= IsuBottomDepth

I have added a small example file with 2 TABLES "OHP_PlotData" and "NPDLithostrat" both added to the Data Model as Links Only... I wish the above INDEX/MATCH combo to be built into PowerQuery adding data to the Column "FORMATION" in the "OHP_PLotData" Table

Can anyone help in writing the correct PowerQuery Code to replicate this, I'm struggling considerably

Thanks

There are instructions at the top of the page explaining how to attach your sample workbook.

Then you need to cut it some more. What's causing the bloat? Do you have full column references in your formulae (e.g. A:A)? Do you have a lot of conditional formatting?

We need only 10-20 rows of sample data in order to be able to offer a solution.

You could load your main lookup table as a query called Formation_NPDLIthostrat, filtering it for only records of FORMATION level. Then amend your OHP_PlotData query to something like:

Please Login or Register  to view this content.

Originally Posted by SimonCampbell

if I actually include all the pertinent data in a "new" query that looks at the tables(queries) will I then only need the "add custom column" script ?

It depends what you mean by that - it's not clear to me at all. You need the lookup table as a query somewhere.

My original problem was caused by very slow performance when I used a FILTER or INDEX/MATCH combo in excel, do you think doing it in PowerQuery will help (or for that matter PowerPivot). In my data set the TABLE NPDLithostrat I have 37000 rows. There's o point in doing this if performance doesn't improve. But I can use the Worrkbook without continually refreshing the connections, only refreshing on OPEN... what do you think?

It won't take long to try it and see, so...