Forum Discussion
I really need a help with finding & matching data from two different sheets.
You have to make sure the ranges are linking to the appropriate sheet and since these are arrays, press Ctrl + Shift + Enter to see the curly brackets { } surrounding the formula. If you continue to have issues, send over your file and I can help apply it
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.