Forum Discussion
I really need a help with finding & matching data from two different sheets.
adversi Hi I uploaded a sample worksheet. I was able to return a value of cell C3 by using your formula, but when I applied to other cell, it returned a wrong value (cell E3 and C5). I'd be really appreciate if you could help finding what went wrong and fixing issues.
here you go - the issue was the Countif reference, it should have been the qty figure rather than the product code
It works perfectly! Thank you so much for your help.
If you don't mind, could you please tell me how this could work?Colinahn
Glad to hear it! Here is the https://www.excelhow.net/how-to-find-nth-occurrence-with-multiple-criteria-using-indexmatch.html I used.The idea is to the get nth Occurrence for a multiple criteria search. However, since there is no unique ID associated, the next best figure to use for a reference lookup is the qty figure.
That being the case, a COUNTIF function is used to check if the figure is occurring for the first time or has already been searched before, and that becomes the nth figure used for the search.
ie. cell C3 and C5 share a qty of 100.
C3 looks into the first row match that returns the correct PO.
C5 accounts that qty = 100 is now the second occurrence, and returns the second row match.
As mentioned in the beginning, the pulls are susceptible to sort changes in Tab 3. To solve this, there will need to be a connection between PO and date.
adversi Thank you for the explanation.
Is there any way that I can make a connection between PO and date? or Qty and date?
What I tried was to link qty. to date on the sheet that has qty. and date, and then tried to make a connection again with that cell to PO. In this way, I thought it would flexibly change even the qty changes or in any modification.
What you suggested works great, but if there is an adjustment made in one sheet, PO also changes or it shows the wrong PO. For example, C3 and C5 shared the same qty let's say 500, but a total qty of C3 got split into 200 and 300 but shares the same PO#. C3 has two same PO and C5 has a different PO. The problem is that the PO sheet does not get an update. Therefore the PO for C5 shifts to C3 (qty 300). If I modify PO data, PO# applies to the correct cell, but it takes time to adjust PO data every time when there is a change.
Please advise if there is any way that I can make a connection between qty and date.