Need to get multiple correspondent value using lookup function

I have 2 datatable, variable1 = lookup value , variable2 = raw data.
1 lookup value can have multiple lines in raw data, and I only want get the values in column “ERP Invoice Number”

What I want to achieve is, lookup the 1st value in variable1 , get all the corresponded value in variable2.

Then write it in the notepad.
Example look up Billing number 462558569 will get array of
3041-5200003479-2022
3041-5200003487-2022
3041-5200003554-2022
3041-5200002374-2022
3041-5200002375-2022
3041-5200002388-2022

Then continue lookup 2nd value in variable1 in variable2… until the last value in variable1.

Below is variable 2, as you can see, 1 Billing number will have multiple ERP Invoice Number
image

I have been searching around, still cant get the answer.
The test I did, the message box produces empty result.
image

you have multiple results
so dont use lookup datatable , because that only gives you 1 result

@jmcheong7
instead use filter data table

then use output datatable to assign the filtered data to string variable e.g. txt

now we can write txt to notepad

if you just want to write erp invoice # to notepad and not the whole filtered table, then instead of usingg output datatable to txt variable, assign

txt = String.Join(Environment.NewLine, filteredDt.AsEnumerable.Select(function(row) row("ERP Invoice Number").toString).ToList)

Hi Jack,

Thanks so much for the help.
I will try it out now.

For the lookup value, I want it be dynamic and reading from the variable (Unique_BillingNo)
And want to perform while loop. to use 1st value in Unique_BillingNo then till the last value.

Could you help on this ?

can you upload the excel @jmcheong7

just a few rows (maybe 1st 20) and include the header

use this
Main (1).xaml (13.6 KB)

result
image

now im writing to text file, if you want to write to webapp, use the txtToWrite variable
image

it looks like that in general it can be solved by grouping the data

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.