Forum Discussion
Solved
I really need a help with finding & matching data from two different sheets.
Hi, I'm trying to search and return the data from two different sheets by using excel formulas such as xlookup, index & match, and so on. What I want to do is that I want to find the PO# value t...
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