Match 3 criterias from table 2 and return "amount" & "stage"

[powr_potato]

Hi All,

May i kindly get your help on an excel formula? I am trying to match 3 criteria between table 1 and table 2, namely: "Order", "Amount" & "Stage" and return the values to Col D & E respectively. For example, if Table 1 Row 3 has "OrderA", Amount of "5" & Stage is "sent" and Table 2 has the same criteria, it should return Amount of "5" in Column D. Really appreciate if i could get your help on this.

[TABLE="width: 1061"]
<colgroup><col span="2"><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]Table1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Table2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Order[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Stage[/TD]
[TD="align: center"]Return Amount if same as table 2[/TD]
[TD="align: center"]Return Stage if same as table 2[/TD]
[TD="align: center"]Order[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Sent[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderF[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"]OrderB[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Handed[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderF[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Sent[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderF[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Handed[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Sent[/TD]
[/TR]
[TR]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]Sent[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]On-Hold[/TD]
[/TR]
[TR]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Handed[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Sent[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"]OrderG[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Handed[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderD[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]On-Hold[/TD]
[/TR]
[TR]
[TD="align: center"]OrderF[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Handed[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderE[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]On-Hold[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Sent[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderB[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Sent[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thank You.
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[eduzs]

d: =IF(AND(A3=F3,B3=G3,C3=H3),B3,"")
E: =IF(AND(A3=F3,B3=G3,C3=H3),C3,"")

 

[powr_potato]

Thanks Eduzs, however, it doesn't work.. I have tried on the first row. In table 1 & 2, there is "OrderA" , Amount of "5" and Stage "Sent", however it doesn't return a value to me.

 

[powr_potato]

Hi All i would like to edit my question. On table one there is Order & Amount. I would like to use an excel formula to fill in column C with "Stage" values if the "Order" & "Amount" matches table 2. Would really appreciate if anyone can help me on this...[TABLE="class: cms_table_grid, width: 1000"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]Table1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Table2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Order[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Return Stage Value[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Order[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderF[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"]OrderB[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderF[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderF[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Sent[/TD]
[/TR]
[TR]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]On-Hold[/TD]
[/TR]
[TR]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"]OrderG[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderD[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]On-Hold[/TD]
[/TR]
[TR]
[TD="align: center"]OrderF[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderE[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]On-Hold[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Sent[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderB[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Handed[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OrderA[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Sent[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[liveinhope]

I have returned the answer in 2 stages
- column C tells you "which row number in table 2 has the same order # and amount (i.e in columns F and G ) as the order # and amount in table2 (columns A and B)


- column D tells you the relevant stage from that row (ie value in column H)

column C formula =MATCH($A2&$B2,$F$2:$F$13&$G$2:$G$13,0)
column D formula =INDEX(H$2:H$13,MATCH($A2&$B2,$F$2:$F$13&$G$2:$G$13,0))

both formulas are array formulas. so instead of ENTER do CTRL-SHIFT-ENTER so that the formulas end up as {=MATCH($A2&$B2,$F$2:$F$13&$G$2:$G$13,0) } and
{=INDEX(H$2:H$13,MATCH($A2&$B2,$F$2:$F$13&$G$2:$G$13,0))} respectively (do not try to enter the curly brackets yourself )

you don't really need the column C formula .. just the column D one but doing it tht way explains it a bit better.


[TABLE="width: 718"]
<tbody>[TR]
[TD]Order[/TD]
[TD]Amount[/TD]
[TD]match order and amt[/TD]
[TD]return stage[/TD]
[TD][/TD]
[TD]Order[/TD]
[TD]Amount[/TD]
[TD]Stage[/TD]
[/TR]
[TR]
[TD]OrderA[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]Sent[/TD]
[TD][/TD]
[TD]OrderF[/TD]
[TD]6[/TD]
[TD]Handed[/TD]
[/TR]
[TR]
[TD]OrderB[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]Handed[/TD]
[TD][/TD]
[TD]OrderF[/TD]
[TD]2[/TD]
[TD]Handed[/TD]
[/TR]
[TR]
[TD]OrderA[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]Sent[/TD]
[TD][/TD]
[TD]OrderF[/TD]
[TD]1[/TD]
[TD]Handed[/TD]
[/TR]
[TR]
[TD]OrderA[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]Sent[/TD]
[TD][/TD]
[TD]OrderA[/TD]
[TD]2[/TD]
[TD]Sent[/TD]
[/TR]
[TR]
[TD]OrderC[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]On-Hold[/TD]
[TD][/TD]
[TD]OrderC[/TD]
[TD]7[/TD]
[TD]On-Hold[/TD]
[/TR]
[TR]
[TD]OrderC[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]Handed[/TD]
[TD][/TD]
[TD]OrderC[/TD]
[TD]4[/TD]
[TD]Handed[/TD]
[/TR]
[TR]
[TD]OrderC[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]Handed[/TD]
[TD][/TD]
[TD]OrderC[/TD]
[TD]3[/TD]
[TD]Handed[/TD]
[/TR]
[TR]
[TD]OrderG[/TD]
[TD]1[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD]OrderD[/TD]
[TD]1[/TD]
[TD]On-Hold[/TD]
[/TR]
[TR]
[TD]OrderF[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]Handed[/TD]
[TD][/TD]
[TD]OrderE[/TD]
[TD]8[/TD]
[TD]On-Hold[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OrderA[/TD]
[TD]5[/TD]
[TD]Sent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OrderB[/TD]
[TD]4[/TD]
[TD]Handed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OrderA[/TD]
[TD]4[/TD]
[TD]Sent[/TD]
[/TR]
</tbody>[/TABLE]

 

[powr_potato]

It works! Thanks you so much!