Formula to extract Value from Sheet

I have value in Col D on sheet "BT8 Intercompany Debtors"


I have tried to set up a formula to extract he value in Col D in the same row as Col B where "Total" occurs for the 1st time below text on this sheet in Col A "Southern & Eastern 8"

Please Login or Register  to view this content.

I get a blank , but result should be -6194.16


Kindly amend my formula

Well, for starters you need to use the correct cell reference in the INDIRECT:

Please Login or Register  to view this content.

Try this:

=LET(c,MATCH("Southern & Eastern 8",INDIRECT("'" & A2 & "'!A:A"),0),f,FILTER(ROW(INDIRECT("'"&A2&"'!B:B")),INDIRECT("'"&A2&"'!B:B")="Total"),INDEX(INDIRECT("'" & A2 & "'!D:D"),XLOOKUP(c,f,f,,1)))

See post #3 for a solution.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Just shout if you need me to explain the formula.

For completeness:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	F	G	H	I	J	K	L
2	c	f	XLOOKUP	INDEX		c	=MATCH("Southern & Eastern 8",INDIRECT("'" & A2 & "'!A:A"),0)
3	14	22	22	-6194.16		f	=FILTER(ROW(INDIRECT("'"&A2&"'!B:B")),INDIRECT("'"&A2&"'!B:B")="Total")
4		30				XLOOKUP	=XLOOKUP(F3,G3#,G3#,,1)
5						INDEX	=INDEX(INDIRECT("'" & A2 & "'!D:D"),H3)
6
7						c	Finds the row on which the criterion appears.
8						f	Finds all of the rows where 'Total' appears.
9						XLOOKUP	Finds the closest row in f that is equal to or larger than c.
10						INDEX	Matches a value in D that matches the row found in the XLOOKUP.

Sheet: Macro

I would not use FILTER with whole column references. It will check EVERY row (1,000,000+++) of them every time it is called in the formula. Given that you HAVE to use INDIRECT, a volatile function that updates EVERY time ANYTHING changes, you may find performance gets very slow. What's wrong simply with this:

=INDEX(INDIRECT("'"&A2&"'!D:D"),MATCH("Total",INDIRECT("'"&A2&"'!B:B"),0))

or have I missed the point somewhere?

... or have I missed the point somewhere?

I believe you have. It's the first occurrance of "Total" AFTER the criterion. The source worksheet is only an excerpt of the data, as far as I am aware.

I would not use FILTER with whole column references.

Neither would I, but it doesn't mean it can't be used. The OP could easily limit the column references.

Indeed. You are probably correct. However, the warning about whole column references in FILTER stands.

Absolutely agree on full column references, but as I said, not my choice.

In that case, I'd use this, which takes 0.004 seconds to calculate 100 cells, as opposed to FILTER, which takes 3.2 seconds to calculate 100 cells.

=LET(A,INDIRECT("'"&$A$2&"'!A:D"),B,MATCH("Southern & Eastern 8",INDEX(A,,1),0),C,INDEX(A,B,2):INDEX(A,1000000,2),D,INDEX(A,B,4):INDEX(A,1000000,4),INDEX(D,MATCH("Total",C,0)))

The end point (red) is irrelevant, as the formula stops when it finds the first match.

This would be an option without full column references:

=LET(c,MATCH("Southern & Eastern 8",INDIRECT("'" & A2 & "'!A1:A10000"),0),f,FILTER(ROW(INDIRECT("'"&A2&"'!B1:B10000")),INDIRECT("'"&A2&"'!B1:B10000")="Total"),INDEX(INDIRECT("'" & A2 & "'!D1:D10000"),XLOOKUP(c,f,f,,1)))

... which reduces calculation time to a much more respectable 0.04 seconds.

Or (to allow for each range to be defined once only):

=LET(a,INDIRECT("'" & A2 & "'!A1:A10000"),b,INDIRECT("'"&A2&"'!B1:B10000"),c,MATCH("Southern & Eastern 8",a,0),d,INDIRECT("'" & A2 & "'!D1:D10000"),f,FILTER(ROW(b),b="Total"),INDEX(d,XLOOKUP(c,f,f,,1)))

Refer to Post 14!!