Multiple IF ANDS?

[gmooney]

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]125000[/TD]
[TD]150000[/TD]
[TD]175000[/TD]
[TD]200000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need a formula in A5,A6,A7 that will say if A1 =B3 AND A2 = B4 then give me B5, if they both don't match move over to column C and see if both those match, if so give me C5 and continuing on until it finds a match. Cells A1 and A2 will always be either 1 or 2 for each cell and cells B3:E4 are constant and will never change. Rows 6,7,8,9, etc will have values in columns B:E.

Any helps is greatly appreciated.

 

[StephenCrump]

Do you mean like this?

C1: =MATCH(1,INDEX((B3:E3=A1)*(B4:E4=A2),1,),)
A5: =INDEX(B5:E5,C$1) copy down

Book1
	A	B	C	D	E
1	1	MatchCol	4
2	2
3		2	2	1	1
4		2	1	1	2
5	200000	125000	150000	175000	200000
6	9	6	7	8	9
7	2	5	4	3	2
Sheet1

 

[gmooney]

Stephen,

This is giving me an #N/A. When I evaluate the formula it shows that B3:E3 are all false?

Do you mean like this?

C1: =MATCH(1,INDEX((B3:E3=A1)*(B4:E4=A2),1,),)
A5: =INDEX(B5:E5,C$1) copy down

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]MatchCol[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]175000[/TD]
[TD="align: right"]200000[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

</tbody>

 

[gmooney]

Stephen,

I got it to work. Your formula does work. Thanks!

 

[StephenCrump]

I got it to work. Your formula does work. Thanks!

Great, you're welcome.