Nov 17 2020 04:46 AM
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?
Nov 17 2020 06:38 AM - edited Nov 17 2020 06:47 AM
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
Nov 17 2020 11:19 PM
Nov 18 2020 02:24 AM
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
Nov 18 2020 06:16 AM
@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.
Nov 18 2020 07:52 AM
Solutionhere you go - the issue was the Countif reference, it should have been the qty figure rather than the product code
Nov 19 2020 02:51 AM
It works perfectly! Thank you so much for your help.
If you don't mind, could you please tell me how this could work?
Nov 19 2020 05:45 AM
@Colinahn
Glad to hear it! Here is the source 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.
Nov 20 2020 02:19 AM
@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.
Nov 20 2020 06:01 AM
The solution will vary based on how the data architecture is set up. For example, if Tab 2 and Tab 3 were downloaded tables or manually created. This is how the tabs are currently connected:
My main question is: Did you create Tab 2 or was it provided?
I would see the end goal to have a table like Tab 3 but with one more column of data to make it easier to work with and complete the connection. This will eliminate the duplicate Tab 2 table and make any changes or updates to the data more manageable and reliable:
From there the formula will change to something much more simpler.
Nov 20 2020 11:51 PM
Tab2 was provided and what I need to do is fill out the row on the Tab2 column.
I agree that it'd be much easier if the date was provided. But since no date is provided, I have to find another way to make it work.
Nov 18 2020 07:52 AM
Solutionhere you go - the issue was the Countif reference, it should have been the qty figure rather than the product code