Find a value after the value already found

[eliozo]

	A​	B​	C​	D​
1​	Country​	Product Size​	Type of product​	Quantity​
2​	USA​	100g​	Regular​	3000​
3​	Brazil​	100g​	Regular​	5000​
4​	USA​	100g​	Regular​	4000​
5​	​	​	​	​
6​	​	​	​	​

	A​	B​
	USA, 100g, Regular​
1​	=IF(AND(A2="USA",B2="100g", C2="Regular"),D2,IF(AND(A3="USA",B3="100g", C3="Regular"),D3,if(…)) à in this case it will display 3000​	I need a formula to find depending on the country, product size and type of product, after the value found in A1, in this example I need to display 4000 and not 3000​

If im not clear enough please let me know.
The second table shows all the quantites of USA , 100g , regular, quantities.. so i want to show all the quantities related to these criterias. Thank you !

 

[DanteAmor]

Check if the following is what you need.
It is an array formula.

Dante Amor
	A	B	C	D	E	F
1	Country	Product Size	Type of product	Quantity		Quantity
2	USA	100g	Regular	3000		4000
3	Brazil	100g	Regular	5000		3000
4	USA	100g	Regular	4000
Hoja2

Cell Formulas
Range	Formula
F2:F4	F2	=IFERROR(INDEX($D$2:$D$4,LARGE(IF("USA|100g|Regular"=$A$2:$A$4&"|"&$B$2:$B$4&"|"&$C$2:$C$4,ROW($A$2:$A$4)),ROWS($G$2:G2))-1),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[eliozo]

What is the G column u entered in the formula ? Because i cant see any G in your book

 

[DanteAmor]

ROWS($G$2:G2)

The formula uses column G only to count the number of data we need to display.
In this example, the formula in F2 shows the first data.
The formula in F3 shows the second data and so on.
Sorry for putting column G in the formula and not showing it on the screen.
But you can use the following make more sense.


=IFERROR(INDEX($D$2:$D$4,LARGE(IF("USA|100g|Regular"=$A$2:$A$4&"|"&$B$2:$B$4&"|"&$C$2:$C$4,ROW($A$2:$A$4)),ROWS($A$2:A2))-1),"")

Note: Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[eliozo]

Weird! Its giving me nothing at all.. help !!

 

[DanteAmor]

Weird! Its giving me nothing at all.. help !!

But if you applied the formula correctly with Control + Shift + Enter, do you get the results you want?

 

[eliozo]

But if you applied the formula correctly with Control + Shift + Enter, do you get the results you want?

yes nothing at all! Just blank

 

[DanteAmor]

Did you try the data sample that I put in post #2?

 

[eliozo]

Did you try the data sample that I put in post #2?

Yes exactly the same

 

[DanteAmor]

I share my file with the formula inserted correctly.
There you will see how the formula works and gets the values 4000 and 3000.

country.xlsx

drive.google.com