3 Criterias Index-Matching

[alexwks]

Dear experts,

I have a question on how I should look up a value for example Profit,B,Q3
How do I index this? If I index it this way: "=INDEX(A1:I5,MATCH(A4,A1:A5,0),MATCH(G2,A2:I2,0),MATCH(F1,A1:I1,0))"
it appears as #REF!

Any advice?

http://www.freeimagehosting.net/89219

 

[Biz]

Try formula below

=INDEX($B$3:$I$5,MATCH(A4,$A$3:$A$5,0),MATCH(F1,$B$1:$I$1,0)+1)

Biz

 

[alexwks]

Thanks Biz, it works if I ignore the A,B part..

My problem now is, I need to have a formula to fill up into the yellow box such as attached...

what is the logic behind +1 at the end anyway?

 

[Marcelo Branco]

Or

A B C D (headers in row 8)
<TABLE style="WIDTH: 211pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=281><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dce6f1; WIDTH: 67pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=89>Rev/Prf/Cash</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Quarter</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Shift</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Result</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Profit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Q1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

Formula in D9
=INDEX($B$3:$I$5,MATCH(A9,$A$3:$A$5,0),MATCH(B9,$B$1:$I$1,0)+(C9="B"))

HTH

M.
ps: hi Biz

 

[Biz]

Thanks Biz, it works if I ignore the A,B part..

My problem now is, I need to have a formula to fill up into the yellow box such as attached...

what is the logic behind +1 at the end anyway?

I have modified formula as per your requirments

=IF($L2="B",INDEX($B$3:$I$5,MATCH($K3,$A$3:$A$5,0),MATCH(L1,$B$1:$I$1,0)+1),INDEX($B$3:$I$5,MATCH($K3,$A$3:$A$5,0),MATCH(L1,$B$1:$I$1,0)))

I am using +1 becuz A has column header and when it is centered I am using it as place holder.

Biz

 

[Biz]

Or

A B C D (headers in row 8)
<TABLE style="WIDTH: 211pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=281><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dce6f1; WIDTH: 67pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=89>Rev/Prf/Cash</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Quarter</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Shift</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Result</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Profit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Q1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR></TBODY></TABLE>

Formula in D9
=INDEX($B$3:$I$5,MATCH(A9,$A$3:$A$5,0),MATCH(B9,$B$1:$I$1,0)+(C9="B"))

HTH

M.
ps: hi Biz

Mate like your formula.

I always like better ways to solve a problems. Thanks for showing another way.

I tried Sumproducts but since qrts1 and not listed both columns I can't make sumproducts work.

 

[Marcelo Branco]

Mate like your formula.

I always like better ways to solve a problems. Thanks for showing another way.

I tried Sumproducts but since qrts1 and not listed both columns I can't make sumproducts work.

Its almost identical to your first one, only changing +1 by +(C9="B")

Same idea.

 

[Biz]

Its almost identical to your first one, only changing +1 by +(C9="B")

Same idea.

Thanks trying to make sumproduct work

 

[Biz]

Sorry formula error


Try

=IF(L$2="B",INDEX($B$3:$I$5,MATCH($K3,$A$3:$A$5,0),MATCH(L1,$B$1:$I$1,0)+1),INDEX($B$3:$I$5,MATCH($K3,$A$3:$A$5,0),MATCH(L1,$B$1:$I$1,0)))

 

[alexwks]

Ah! I see!
Thanks for both inputs..
the actual file i am working on is actually more than A & B, its more like a company departments so I have like 16 of them

But these could be a base for me to get what I want.

Thanks again!