looking for a formula to lookup a value based on multiple criteria in multiple sheets

[nihad]

Hello

I'm looking for a formula to lookup a value based on multiple criteria in multiple sheets

for example Tab1 one

ALL test lookup.xlsx
	A	B	C	D
1				103-01
2		Period End		24-Mar-21
3		Standing Order		£ 10,997.84
4
61
62	TOTAL OVERHEADS	7,787.68	0.00	7,787.68
63
64
65	Operating Cost/(Credit)	7,024.24	0.00	7,024.24
Tab1

Tab2

ALL test lookup.xlsx
	A	B	C	D
1	Aurora - Cafe & Facilities			105-01
2		Period End		24-Mar-21
3		Standing Order		£ 0.00
4
52
53
54
55	Operating Cost/(Credit)	2,481.64	4,865.03	(2,383.39)
Tab2

then I will have a summary sheet where I want to do the lookup based on Cell D1 in Tab1 and Tab2 those 2 are

then my second criteria would be based on "Operating Cost/(Credit)" and this can be on any of the rows but always in column A

ALL test lookup.xlsx
	B	C
73	Location Number	Operating Cost/(Credit)
74	103	lookup return value
75	105	lookup return value
Tab2

also to add the tab names changes all the time

Thanks

 

[nihad]

Any help with this?

Even a Marco would do