Need Formula to find the match then copy & paster number

[tuongtu3]

Hello All,
I'm looking for the Formula to find the match between 2 ws, if there's any matches then copy and paste the number base on the select range. Please take a look at sample in attached for more clear.

Regards,
tt3

[benishiryo]

hi tt3. try this in W2:
=IFERROR(LOOKUP(2,1/((Sheet2!$G$2:$G$20=F2)*(Sheet2!$M$2:$M$20<=X2)),Sheet2!$R$2:$R$20),0)

[tuongtu3]

Hi benishiryo,
The code works perfect. I don't understand at all why it work when I look at the formula. Ca you explain a little so I can learn.

Regards,
tt3

[tuongtu3]

Hi benishiryo,
Just found out that the formula return value form the bottom to top. Is there any way to search the value from top to bottom or I have to sort value from small to larger.

To ensure the formula work correct I would like to matches the third column and please look at revise-sample in attached.

Regards,
tt3

Regards,
tt3

[tuongtu3]

Hi benishiryo,
Just found out that the formula does not get result when qty does not match. Please take a look at cell W3 and W16 in attahcced file.

Regards,
tt3

[tuongtu3]

Hi benishiryo,
Just found out that the formula does not get result when qty does not match. Please take a look at cell W3 and W16 in attached file.

Regards,
tt3

[AlKey]

Hi benishiryo,
Just found out that the formula does not get result when qty does not match. Please take a look at cell W3 and W16 in attached file.

Regards,
tt3

Maybe conditions of the data rather ambiguous? "cell W16 should be 1" Why?

[tuongtu3]

Hi AlKey,

"cell W16 should be 1" Why?

I would like to scan the result from top to bottom but I can solve this issue by sorting value from Largest to smallest.

How about the Cell W3? It would be perfect if it can scan and get the value of 99 (Currently I have to use another step of formula Vlookup).

Regards,
tt3

[FDibbins]

Try this approach using a helper column on sheet2 (I used W)...
copy this to W2 and copy down...
=G2&COUNTIF($G$2:G2,G2)
Then use this on sheet1 to pull in the values...
=IFERROR(INDEX(Sheet2!$R$2:$R$20,MATCH(Sheet1!F2&COUNTIF(Sheet1!$F$2:F2,Sheet1!F2),Sheet2!$W$2:$W$20,0)),"")

[AlKey]

This will get value 99

=IFERROR(LOOKUP(2,1/((Sheet2!$G$2:$G$24=F2)*(Sheet2!$M$2:$M$24<=X2)),Sheet2!$R$2:$R$24),IFERROR(VLOOKUP(F2,Sheet2!$G$2:$R$24,12,0),0))

[tuongtu3]

@DibBins: Formula does not work properly at W2, W3, W9, W13, W14, W16.
@AlKey: Formula works perfect.

Regards.
tt3

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[tuongtu3]

Thank you very much for all of your time and help.
Reputation has been added.
Regards,
tt3