Look up value between values in another table

Question

Hi -

Okay, I have a table of orders for a customer:

Customer 112345678Total325168421601355267408411

1 - 8 represents months. 1 = Jan, etc...

I have another table of payment levels:

JanFebMarAprMayJunJulAugLevel 2118100125203188280267133Level 3245170265311287356321286Level 4361192321422344498455369Level 5399212400537498521528444Level 6425294497600544600679516

For each month I need to look up the Total value in table 2 and return which level it has reached and how many more orders are required to reach the next level.

So, in the example above for January. The customer has placed 325 orders. This would place them in Level 3. They would require 36 more orders to reach Level 4.

So, ultimately, I'd be looking for an output table that looks something like this:

JanFebMarAprMayJunJulAugCurrent Level32564 34Diff. to Next Level36276 143134733

Any ideas on how this could be done I'd be very, very grateful!

Thanks

RDF

Prometheus · Accepted Answer

@RDF25087 This was a fun one. Basically, I pivoted both tables so I could join on month, then used the Multi-Row Formula tools to determine level and difference to next level, then pivoted the data again to create the desired output.