Seeking a Less Resource Intense Lookup Formula

[The_Snook]

Afternoon all,

I've started to create a model but two of the formulas I've written appear to be resource draining and I'm hoping someone can conjure up a more efficient solution? Attached is an example of the data and structure I'm working with. The 'Services' tab defines which buildings require which services. The 'Rates' tab identifies which services are required against which building and provides an input field to captures the cost. The problem I have is that some estates may be up to 400 buildings and the number of services available is 170+, so the data could span over 70k rows and Excel doesn't like this approach. It's fine whilst the workbook is open but if I save and try to reopen the document later it won't have it. Sometimes I get an error message saying it's too resource intensive, other times it simply closes Excel.

The formulas I'm looking to improve are in columns C & D of the 'Rates' tab (highlighted orange).

Thanks in advance,

Snook

[Fluff13]

For col D, how about

Please Login or Register  to view this content.

[MarvinP]

Hi Snook,

See if my answer using Power Query Unpivot works for you.
PQ Unpivot 29.05.22 Less Resource Intense Lookup Formula.xlsx

[The_Snook]

@Fluff13 - Thanks for this, much appreciated. I don't want to count my chickens but your alternative solution appears a lot more palatable to XL. It's opening the workbook with relative ease now. Great shout on the T function, I'd never even heard of it.

@MarvinP - Thanks MarvinP, I'm always grateful for a PQ solution. Unfortunately in this instance I'm not the sole end user (it's a template for others), otherwise I'd have been happy to use it.

Snook

[Fluff13]

Glad to help & thanks for the feedback.

[Bo_Ry]

Please try at
A2:B2
=INDEX(Services!B$3:B$27,MOD(ROWS(A$2:A2)-1,25)+1)


D2
=INDEX(Services!$D$3:$M$27,MOD(ROWS(D$2:D2)-1,25)+1,(ROWS(D$2:D2)-1)/25+1)&""


or MS365
=LET(z,Services!$B$2:$M$27,r,ROWS(z)-1,c,COLUMNS(z)-2,s,SEQUENCE(r*c,,0),rs,INDEX(z,(MOD(s,r)+2)^{1,1,0,1},(s/r+3)^{0,0,1,1}+{0,1,0,0})&"",rs)