Kindly share Formula for get date from Multiple invoice numbers date to one invoice number

[bhanurapelli]

Dear,

Kindly share Formula for get date from Multiple invoice numbers date to one invoice number;

Multiple Invoice Numbers	Document Receipt date		Search Invoice Number	Document Receipt date
100457692/100457707/100457708	17.10.2022		100457708	???
100457856/100457857	18.10.2022		100457717	???
100457013/100457717/100457718/100457719	19.10.2022		100457711	???
			100457718	???
			100457857	???

 

[AlanSidman]

Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document Receipt date", type date}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Multiple Invoice Numbers", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Multiple Invoice Numbers")
in
    #"Split Column by Delimiter"

Excel 2016 (Windows) 64 bit

	A​	B​	C​	D​	E​
1​	Multiple Invoice Numbers	Document Receipt date		Multiple Invoice Numbers	Document Receipt date
2​	100457692/100457707/100457708	10/17/2022​		100457692	10/17/2022​
3​	100457856/100457857	10/18/2022​		100457707	10/17/2022​
4​	100457013/100457717/100457718/100457719	10/19/2022​		100457708	10/17/2022​
5​				100457856	10/18/2022​
6​				100457857	10/18/2022​
7​				100457013	10/19/2022​
8​				100457717	10/19/2022​
9​				100457718	10/19/2022​
10​				100457719	10/19/2022​

Sheet: Sheet3

 

[Peter Bartholomew]

As a formula to look up dates for the specified order numbers, you could us a wildcard XLOOKUP

= XLOOKUP("*"&invNum&"*", Multiple, received,"Not known",2)

To do as @AlanSidman has done with Power Query and extract a full list of Invoice Numbers is much harder

= LET(
      combined, MAP(multiple, received,
         LAMBDA(m,r, TEXTJOIN(";",,TEXTSPLIT(m,"/")&","&TEXT(r,"dd/mm/yy")))),
      list, REDUCE({" Number"," Receipt date"}, combined,
         LAMBDA(output,pairs, VSTACK(output, TEXTSPLIT(pairs,",",";")))),
      SORT(list,,1)
      )

 

[bosco_yip]

Maybe,

In E2, formula copied down:

=IFERROR(LOOKUP(1,0/FIND(D2,A$2:A$99),B$2:B$99),"")

 

[bhanurapelli]

Maybe,

In E2, formula copied down:

=IFERROR(LOOKUP(1,0/FIND(D2,A$2:A$99),B$2:B$99),"")

View attachment 81395

Thank you very much....