SOLVED

I really need a help with finding & matching data from two different sheets.

Copper Contributor

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

Screen Shot 2020-11-17 at 21.40.20.png

I need to fill out yellow highlighted cells by using two different data from sheet2 and sheet3.

Sheet2

Screen Shot 2020-11-17 at 21.39.25.png

Sheet3 

Screen Shot 2020-11-17 at 21.40.51.png

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

@Colinahn 

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))))

 

 

adversi_0-1605624439367.png

 

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?

@Colinahn 

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.

best response confirmed by Colinahn (Copper Contributor)
Solution

@Colinahn 

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 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.

@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.

@Colinahn 

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:

adversi_1-1605880560153.png

 

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:

 

adversi_0-1605880510914.png

 

From there the formula will change to something much more simpler.

@adversi 

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.

 

1 best response

Accepted Solutions
best response confirmed by Colinahn (Copper Contributor)
Solution

@Colinahn 

here you go - the issue was the Countif reference, it should have been the qty figure rather than the product code 

View solution in original post