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.

A B C D E F G H Table1 Table2 Order Amount Stage Return Amount if same as table 2 Return Stage if same as table 2 Order Amount Stage OrderA 5 Sent OrderF 6 Handed OrderB 4 Handed OrderF 2 Handed OrderA 4 Sent OrderF 1 Handed OrderA 2 Handed OrderA 2 Sent OrderC 7 Sent OrderC 7 On-Hold OrderC 4 Handed OrderC 4 Handed OrderC 3 Sent OrderC 3 Handed OrderG 1 Handed OrderD 1 On-Hold OrderF 2 Handed OrderE 8 On-Hold OrderA 5 Sent OrderB 4 Handed OrderA 4 Sent <tbody> </tbody>
Thank You.

<colgroup><col span="2"><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[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...

A	B	C	D	E	F	G	H
Table1					Table2
Order	Amount	Return Stage Value			Order	Amount	Stage
OrderA	5				OrderF	6	Handed
OrderB	4				OrderF	2	Handed
OrderA	4				OrderF	1	Handed
OrderA	2				OrderA	2	Sent
OrderC	7				OrderC	7	On-Hold
OrderC	4				OrderC	4	Handed
OrderC	3				OrderC	3	Handed
OrderG	1				OrderD	1	On-Hold
OrderF	2				OrderE	8	On-Hold
					OrderA	5	Sent
					OrderB	4	Handed
					OrderA	4	Sent

<tbody>
</tbody>

 

[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.

Order	Amount	match order and amt	return stage		Order	Amount	Stage
OrderA	5	10	Sent		OrderF	6	Handed
OrderB	4	11	Handed		OrderF	2	Handed
OrderA	4	12	Sent		OrderF	1	Handed
OrderA	2	4	Sent		OrderA	2	Sent
OrderC	7	5	On-Hold		OrderC	7	On-Hold
OrderC	4	6	Handed		OrderC	4	Handed
OrderC	3	7	Handed		OrderC	3	Handed
OrderG	1	#N/A	#N/A		OrderD	1	On-Hold
OrderF	2	2	Handed		OrderE	8	On-Hold
					OrderA	5	Sent
					OrderB	4	Handed
					OrderA	4	Sent

<tbody>
</tbody>

 

[powr_potato]

It works! Thanks you so much!