Looking up based off word after another word

[erniepoe]

Hi All,

I would like to do an Index/Match, but have it look for the first subtotal after a specific persons name.

So my column data looks like this:

John Doe
Blank
Blank
Blank
Subtotal
Jane Doe
Blank
Blank
Blank
Blank
Blank
Subtotal
Jason Doe
Blank
Blank
Subtotal

The number of blank rows after a persons name are dependent on how many line items associated with them, and will vary by person.

Is there a way I can alter the formula to find the first subtotal after Jane Doe, and then find the value in column R? Thanks so much.

=INDEX(R:R,MATCH("subtotal",B:B,0))

 

[Fluff]

How about

Fluff.xlsm
	A	B																R	S	T	U
1
2		John Doe																1		jane doe	12
3		Blank																2
4		Blank																3
5		Blank																4
6		Subtotal																5
7		Jane Doe																6
8		Blank																7
9		Blank																8
10		Blank																9
11		Blank																10
12		Blank																11
13		Subtotal																12
14		Jason Doe																13
15		Blank																14
16		Blank																15
17		Subtotal																16
18
Sheet6

Cell Formulas
Range	Formula
U2	U2	=LET(f,FILTER(B2:R1000,(B2:B1000=T2)+(B2:B1000="subtotal")),INDEX(TAKE(f,,-1),XMATCH(T2,TAKE(f,,1))+1))

 

[erniepoe]

Thank you Fluff, that worked great! Cheers!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[erniepoe]

Fluff, does it have to be referencing the same tab to work? I turned your formula into this and and is returning a value error, is there a way to fix (or something I am doing wrong?


=LET(f,FILTER('Bookings - Jan 2024'!B2:R1000,('Bookings - Jan 2024'!B2:R1000=E6)+('Bookings - Jan 2024'!B2:R1000="Subtotal")),INDEX(TAKE(f,,-1),XMATCH(E6,TAKE(f,,1))+1))

 

[Eric W]

I'll let Fluff figure out the issue with that formula, but here's another you can try. You should see where to put sheet references.

Book2
	A	B																R	S	T	U
1
2		John Doe																1		Jane Doe	12
3																		2
4																		3
5																		4
6		Subtotal																5
7		Jane Doe																6
8																		7
9																		8
10																		9
11																		10
12																		11
13		Subtotal																12
14		Jason Doe																13
15																		14
16																		15
17		Subtotal																16
Sheet8

Cell Formulas
Range	Formula
U2	U2	=VLOOKUP("Subtotal",OFFSET(Sheet8!B2:R1000,MATCH(T2,Sheet8!B2:B1000,0),0),17,0)

 

[Fluff]

The criteria range should be a single column, so it would be like

=LET(f,FILTER('Bookings - Jan 2024'!B2:R1000,('Bookings - Jan 2024'!B2:B1000=E6)+('Bookings - Jan 2024'!B2:B1000="Subtotal")),INDEX(TAKE(f,,-1),XMATCH(E6,TAKE(f,,1))+1))

 

[erniepoe]

ah, thank you, Fluff. Silly error on my part. Cheers!

 

[Fluff]

Glad we could help & thanks for the feedback.