Match to a Variable List populate by Concat of a Field

[shane_spencer]

I've got two tables that I need to compare for reconciliation purposes. I can compare these at a high level because I have a key withing each set of data that matches but I where there's a discrepancy I want to drill down and look at the Delivery vs Invoice number to see matches.

Therefore my idea was to use create a variable vMatchVAT based on the list of VAT return NET Values

i.e. Concat(AMT_NET_TAXREP_src_1,',')

This gives me in this example 625.60,977.60 (see the blue text box)

I thought it would be a simple just to put this in to an expression using Match to compare the two:

i.e. If(Match(GOODS_INVOICE_VALUE,$(vMatchVAT)),1,0)

However for some reason this does not work. If I replace the variable with the list of numbers 625.60,977.60 it works. Or if I replace the concat formula in the variable with the numbers 625.60,977.60 it works.

My overall goal is to get a formula that will match the values in Intrastat with those in VAT in both tables/charts and highlight the matching values green.

Can anyone help?

[swuehl · Accepted solution]

Define your variable with a leading equal sign:

The Magic of Variables

[shane_spencer]

I feel like such a dope! lol Thanks Stefan!