Forum Discussion
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 that has the correct date by using data from two different sheets.
Sheet1
I need to fill out yellow highlighted cells by using two different data from sheet2 and sheet3.
Sheet2
Sheet3
I'm having trouble because sheet 3 does not provide the date, and sheet2 is frequently modified.
My question is what function can I use to search and return the correct PO# that has a correct date?
or is there any other way such as python or VBA that I can solve this problem?
10 Replies
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
- Hi, Thanks for the reply.
I tried (literally copied and pasted) your formula, but it showed #NUM error.
Any reason why?
