Guys,
I have two sheets:
Sheet "Riscos" where I have several rows with different risks and one range/column named "Departamento" (Department, in English). Each department can have several risks.
Sheet "Riscos_formula_extraction"
On Sheet Extraction, I want to extract data from sheet "Riscos" based on a criteria which will on the cell A1 of Sheet "Riscos_formula_extraction".
In order to do that, I was trying to use the following formula
However, the part in red is confusing me because its' not working as expected.
This it the overall layout of sheet "Riscos_formula_extraction"
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]COM (name of the department I want to look data for)[/TD]
[TD]Nº of the risk[/TD]
[/TR]
[TR]
[TD]1 (# of the instance I want to look up for)[/TD]
[TD]FORMULA ABOVE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any hint on how to go around this issue?
I have two sheets:
Sheet "Riscos" where I have several rows with different risks and one range/column named "Departamento" (Department, in English). Each department can have several risks.
Sheet "Riscos_formula_extraction"
On Sheet Extraction, I want to extract data from sheet "Riscos" based on a criteria which will on the cell A1 of Sheet "Riscos_formula_extraction".
In order to do that, I was trying to use the following formula
Code:
=INDEX(Riscos!$B$3:$AK$223;MATCH(Riscos_formula_extraction!$A2&Riscos_formula_extraction!$A$1;[B][COLOR=#ff0000](ROW(A2)-1)[/COLOR][/B]&Departamento;0);MATCH(Riscos_formula_extraction!J$1;Riscos!$B$2:$AK$2;0))
However, the part in red is confusing me because its' not working as expected.
This it the overall layout of sheet "Riscos_formula_extraction"
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]COM (name of the department I want to look data for)[/TD]
[TD]Nº of the risk[/TD]
[/TR]
[TR]
[TD]1 (# of the instance I want to look up for)[/TD]
[TD]FORMULA ABOVE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any hint on how to go around this issue?