Search for a text and then search for another text after that cell

[AJESHKDY]

Hello all,

I have attached a sample sheet to better understand my problem.

What I need is, search for "Location: KL" in column A of Sheet1 and then search for "Product 1" in the same column, but include only the cells after the text "Location: KL" in the search. Then get the "Product Quantity" value corresponding to "Product 1").

The formula is to be in B2 of Sheet2.

Any help is appreciated.

Sheet1:

sample.xlsx
	A	B
1	Description	Product Quantity
2	some text	some text
3	some text	some text
4	some text	some text
5	Location: TN
6	Product 2	20
7	Product 5	10
8	Product 8	15
9	Location: AP
10	Product 2	10
11	Product 1	45
12	Location: KL
13	Product 5	15
14	Product 1	30
15	Product 2	25
16	Product 8	25
Sheet1

Sheet2:

sample.xlsx
	A	B
1	Description	Product Quantity
2	Product 1	equal to -> search for "Product 1" under "Location: KL" in Sheet1 and get Product Quantity (30).
3	Product 2
4	Product 3
5	Product 4
6	Product 5
7	Product 6
8	Product 7
9	Product 8
10	Product 9
11	Product 10
Sheet2

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Search for a text and then search for another text after the row containing the first text
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.