• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup from several columns and result

F

Fangyuan Liu4196058lfy

New Member
I want to use lookup formula to return the "pick up date" by using "PO number" as reference.

Data source is like below, several"PO number"has same "pick up date ". This is recorded by our transportation forwarder.

59400

And I want to get the result in another list for power BI purpose, below in red word is my expected result. I tried Vlookup and index match, all failed because some of the cells are empty and sometimes the lookup column is not in first column of searching array.
59401

Can some one help to find a formula to solve this? Thanks in advance.
 

Attachments

  • 1.xlsx
    10.8 KB · Views: 4
Hi:

I want more information on this:

Are you looking it based on only column A?
What is the relevance of columns B,C &D?
Which Pickup date you want to look up if 1 PO is having multiple pick dates?
could you fill in expected results for couple more PO numbers?

Thanks
 
Nebu said:
Hi:

I want more information on this:

Are you looking it based on only column A?
What is the relevance of columns B,C &D?
Which Pickup date you want to look up if 1 PO is having multiple pick dates?
could you fill in expected results for couple more PO numbers?

Thanks
Click to expand...
 

Attachments

  • 2.xlsx
    10.9 KB · Views: 5
  • 2.xlsx
    10.9 KB · Views: 3
Column A, B, C and D are same level, they are all PO numbers sharing same pick up date.
I am looking it based on A, B, C, and D, some times my result PO appears in A, sometimes in B, and ...
if multiple pick up dates, I want the latest pick up date.

thanks for your quick reply and help.
 

Attachments

  • 2.xlsx
    10.9 KB · Views: 2
Formula solution

1] In sheet2 B2 formula copied down :

=IF($A2="","",INDEX(Sheet1!$E$1:$E$100,AGGREGATE(14,6,ROW(Sheet1!$A$1:$A$100)/(Sheet1!$A$1:$D$100=$A2),1)))

2] For testing purpose, I added a duplicate PO in Sheet1 D25, and returned the latest pick up date of "22/09/2016".

Regards
Bosco
 

Attachments

  • PickUpDate.xlsx
    11.9 KB · Views: 6
bosco_yip said:
Formula solution

1] In sheet2 B2 formula copied down :

=IF($A2="","",INDEX(Sheet1!$E$1:$E$100,AGGREGATE(14,6,ROW(Sheet1!$A$1:$A$100)/(Sheet1!$A$1:$D$100=$A2),1)))

2] For testing purpose, I added a duplicate PO in Sheet1 D25, and returned the latest pick up date of "22/09/2016".

Regards
Bosco
Click to expand...


Hello,
I tried in my final report using your formula. some problem happen.
59410

My formula is IF($A2="","",INDEX('C:\Users\fangyuan.liu\Desktop\CWEC备份-20190220\CWEC\000-产假后\Weekly availability review\Daily check\Data\[Logistic report.xlsx]Sheet1'!$E$1:$E$10000,AGGREGATE(14,6,ROW('C:\Users\fangyuan.liu\Desktop\CWEC备份-20190220\CWEC\000-产假后\Weekly availability review\Daily check\Data\[Logistic report.xlsx]Sheet1'!$E$1:$E$10000)/('C:\Users\fangyuan.liu\Desktop\CWEC备份-20190220\CWEC\000-产假后\Weekly availability review\Daily check\Data\[Logistic report.xlsx]Sheet1'!$E$1:$L$10000=$A2),1)))

The difference between my formal report and template I copied for help is:
In template source data PO.. are in column A ~column D, but in formal report they are listed from column E to column L (extracting from text in Column D using formula mid).

59412

59411
 
1] Without seeing your actual file, it is difficult to find the reason of error causing.

But, as per described, I guess the mistakes maybe caused by the extracting formulae (MID) used in Column E to Column L

2] As per your attached file in Post #.1, your formula In Column D to extract data from Column D
>> =IF(ISBLANK(LEFT(C2,13)),0,LEFT(C2,13))

Basically, it is a non-robust formula, and easy caused mistakes in difference text length

3] The other reason is in the Post #.6 used AGGREGATE function which is introduced since Excel 2010, Excel 2007 and below is not available of this function.

Regards
Bosco
 
Last edited:
Fangyuan Liu4196058lfy said:
Hello,
I tried in my final report using your formula. some problem happen.
View attachment 59410

My formula is IF($A2="","",INDEX('C:\Users\fangyuan.liu\Desktop\CWEC备份-20190220\CWEC\000-产假后\Weekly availability review\Daily check\Data\[Logistic report.xlsx]Sheet1'!$E$1:$E$10000,AGGREGATE(14,6,ROW('C:\Users\fangyuan.liu\Desktop\CWEC备份-20190220\CWEC\000-产假后\Weekly availability review\Daily check\Data\[Logistic report.xlsx]Sheet1'!$E$1:$E$10000)/('C:\Users\fangyuan.liu\Desktop\CWEC备份-20190220\CWEC\000-产假后\Weekly availability review\Daily check\Data\[Logistic report.xlsx]Sheet1'!$E$1:$L$10000=$A2),1)))

The difference between my formal report and template I copied for help is:
In template source data PO.. are in column A ~column D, but in formal report they are listed from column E to column L (extracting from text in Column D using formula mid).



View attachment 59411
Click to expand...
bosco_yip said:
1] Without seeing your actual file, it is difficult to find the reason of error causing.

But, as per described, I guess the mistakes maybe caused by the extracting formulae (MID) used in Column E to Column L

2] As per your attached file in Post #.1, your formula In Column D to extract data from Column D
>> =IF(ISBLANK(LEFT(C2,13)),0,LEFT(C2,13))

Basically, it is a non-robust formula, and easy caused mistakes in difference text length

3] The other reason is in the Post #.6 used AGGREGATE function which is introduced since Excel 2010, Excel 2007
bosco_yip said:
1] Without seeing your actual file, it is difficult to find the reason of error causing.

But, as per described, I guess the mistakes maybe caused by the extracting formulae (MID) used in Column E to Column L

2] As per your attached file in Post #.1, your formula In Column D to extract data from Column D
>> =IF(ISBLANK(LEFT(C2,13)),0,LEFT(C2,13))

Basically, it is a non-robust formula, and easy caused mistakes in difference text length

3] The other reason is in the Post #.6 used AGGREGATE function which is introduced since Excel 2010, Excel 2007 and below is not available of this function.

Regards
Bosco
Click to expand...

Hello,
Now I attached the actual file by deleting some information. please help to check and resolve.

Data source is in logistic report excel, below circled are "PO" and "pick up date"
59422

Result file is purchaseorder excel, Column A is the "PO", sorry that the title in Chinese. and after lookup, I want the corresponding pick up date is returned to column AO in this excel.

59423
Click to expand...
 

Attachments

  • 1554966664634.png
    1554966664634.png
    11.1 KB · Views: 4
  • 1554967081229.png
    1554967081229.png
    4 KB · Views: 5
  • Logistic report.xlsx
    262.4 KB · Views: 4
As per your attached file, your Lookup list criteria does not match with the Source data list, please take a look below picture :

59428

Regards
Bosco
 
bosco_yip said:
As per your attached file, your Lookup list criteria does not match with the Source data list, please take a look below picture :

View attachment 59428

Regards
Bosco
Click to expand...

Hello,
Sorry for the mistake, because of the actual file is too large, not allow me to upload. So I delete most and reserve only several row resulting in the criteria not match.

Now I prepare a new one, please help to check.
 

Attachments

  • PurchaseOrder1.xlsx
    9.8 KB · Views: 4
  • Logistic report.xlsx
    260.7 KB · Views: 3
You must log in or register to reply here.
Back
Top