Forum Discussion
I really need a help with finding & matching data from two different sheets.
This one is a bit tricky since Sheet3 will be vulnerable to any sort/order changes.
The initial solution is to use INDEX/MATCH that uses multiple conditions and the qty as the Unique ID (UID) - not the best recommendation to go about it.
Creating an array with multiple conditions that you can control the nth value of the result-
=INDEX($D$4:$D$7,
IF(COUNTIF($E12:E12,E12)>1,
SMALL(IF($E$4:$E$7=E12,
IF($C$4:$C$7=$C$12,
ROW($C$4:$C$7)-ROW(INDEX($C$4:$C$7,1,1))+1)),COUNTIF($E12:E12,E12)),
SMALL(IF($E$4:$E$7=E12,
IF($C$4:$C$7=$C$12,
ROW($C$4:$C$7)-ROW(INDEX($C$4:$C$7,1,1))+1)),COUNTIF($E12:E12,E12))))
The recommended route is to create a link PO and date in Sheet3
I tried (literally copied and pasted) your formula, but it showed #NUM error.
Any reason why?
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.