LinkBack URL
About LinkBacks
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
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
Last edited by tuongtu3; 10-27-2013 at 07:39 PM. Reason: Solved
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)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
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
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
Last edited by tuongtu3; 10-17-2013 at 11:00 AM.
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?Originally Posted by tuongtu3
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
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
Hi AlKey,
I would like to scan the result from top to bottom but I can solve this issue by sorting value from Largest to smallest."cell W16 should be 1" Why?
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
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)),"")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
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))
@DibBins: Formula does not work properly at W2, W3, W9, W13, W14, W16.
@AlKey: Formula works perfect.
Regards.
tt3
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).
Thank you very much for all of your time and help.
Reputation has been added.
Regards,
tt3
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks