Look up value between values in another table

[RDF25087]

Hi -

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

Customer 1	1	2	3	4	5	6	7	8
Total	325	168	421	601	355	267	408	411

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

I have another table of payment levels:

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
Level 2	118	100	125	203	188	280	267	133
Level 3	245	170	265	311	287	356	321	286
Level 4	361	192	321	422	344	498	455	369
Level 5	399	212	400	537	498	521	528	444
Level 6	425	294	497	600	544	600	679	516

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:

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
Current Level	3	2	5	6	4		3	4
Diff. to Next Level	36	2	76		143	13	47	33

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

Thanks

RDF

[Prometheus · Accepted solution]

@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.

 

[RDF25087]

@PrometheusThank you for the response! I'm glad you found the challenge fun - I shut down my laptop in disgust on Friday as the issue was a huge thorn in my side, so I'm very grateful for your accurate and elegant response!

RDF