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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#4472C4]Multiple Invoice Numbers[/td][td=bgcolor:#4472C4]Document Receipt date[/td][td][/td][td=bgcolor:#70AD47]Multiple Invoice Numbers[/td][td=bgcolor:#70AD47]Document Receipt date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#D9E1F2]100457692/100457707/100457708[/td][td=bgcolor:#D9E1F2]

10/17/2022​

[/td][td][/td][td=bgcolor:#E2EFDA]100457692[/td][td=bgcolor:#E2EFDA]

10/17/2022​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]100457856/100457857[/td][td]

10/18/2022​

[/td][td][/td][td]100457707[/td][td]

10/17/2022​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#D9E1F2]100457013/100457717/100457718/100457719[/td][td=bgcolor:#D9E1F2]

10/19/2022​

[/td][td][/td][td=bgcolor:#E2EFDA]100457708[/td][td=bgcolor:#E2EFDA]

10/17/2022​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td]100457856[/td][td]

10/18/2022​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]100457857[/td][td=bgcolor:#E2EFDA]

10/18/2022​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][td]100457013[/td][td]

10/19/2022​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]100457717[/td][td=bgcolor:#E2EFDA]

10/19/2022​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td][/td][td][/td][td]100457718[/td][td]

10/19/2022​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]100457719[/td][td=bgcolor:#E2EFDA]

10/19/2022​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

 

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