Searching the values with mulltiple condition

[Kiran1683]

Hello Champions,
I have a query, where I need to search a value from table with multiple condition. Using the Vlookup, I can get only look/search only one value, however, I need to search with two conditions.

Here is Example:

Database:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[TD]P[/TD]
[/TR]
</tbody>[/TABLE]

Calculation:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Ref 1
[/TD]
[TD]Ref 2
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[TD]P
[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[TD]Q[/TD]
[/TR]
</tbody>[/TABLE]


I need to get the results as marked in red. Where I need to search Ref 1 and Ref 2 with database table and get the corresponding result to calculation table, result column. Both Ref 1 and Ref 2 conditions should be true. Please help me to get this values

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX($C$2:$C$6,MATCH("A",IF($B$2:$B$6=4,$A$2:$A$6),0))

 

[Andrew Poulsom]

Another possibility:

Excel 2010
	A	B	C	D	E	F
1	Col A	Col B	Result	Ref 1	Ref 2	Result
2	A	1	X	A	4	P
3	B	2	Y	B	2	Y
4	C	3	Z	B	4	Q
5	B	4	Q
6	A	4	P
Sheet2

Cell Formulas
Range	Formula
F2	=INDEX(C$2:C$6,MATCH(1,INDEX((A$2:A$6=D2)*(B$2:B$6=E2),),FALSE))

 

[Kiran1683]

Hello Aladin and Andrew..

You guys are super fantastic.. Thanks, it working awesome..