On comparing fields, how to get the number of fields that have matched?

[#1]

I have 4 fields in a table A and 5 fields in a table B.
I am comparing each of the 5 fields in table B to each of the 4 fields in table A because in a particular row, a value in any field in table B can match a value in any field in table A.
So how can i get the number of fields of table B that matched ?

     A1    |    A2   |   A3   |   A4                                      B1   |   B2   |   B3   |   B4   |   B5

      3          4          5         6                                        5          6         7         8         3

here for this particular row, the answer will be 3, i.e. A1=B5, A3=B1, A4=B2.
So how can I get this count?
Please help.

[ahaahaaha · Accepted solution]

Hi,

May be like as in attached file.

Regrds,

Andrey

[#3]

Thank you so much, sir.

One addition, we can concatenate a space with Text(B1), Text(B2),.............. also, like

Text(B1)&' '

so that when B1 is a substring of A1 or A2 or any of them, that doesn't count.

example :-

if A1 has a value 67 and B1 has a value 6, that'll be counted as a match, but it shouldn't be. Adding a space at the end of B columns solves that.

I hope you understood what i meant. I'm sorry if I couldn't explain properly.

Thanks a lot for answering and solving my problem. Made my day.