Multiple cell reference/Address for matching values in two columns using address & Match formula

[confused_09]

I have a sheet where in Column A1:A1001 there is a list of numbers, similarly in column B1:B1001 there is another list of numbers. I wish to see which of the numbers in Column A are there in Column B and then have the absolute cell address / reference of these matching numbers present in column B. I used this formula in C1 (=ADDRESS(MATCH(A1,$B$1:$B$1001,0),2,1,1) )
and filled in the rest of the rows till C1001, so that i can have the cell reference of matching number in Column B in the same row as the Column A number for which i am searching the match in column B.

I am getting the cell reference in column C only for the first match, not for the subsequent matches, how can I have all the cell references where this match is occurring?

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Your formula worked for me

3	4	$B$2
5	3	$B$3
10	5	$B$4
4	10	$B$1

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

M.

 

[confused_09]

Thanks for your reply.

I would like to have results as in the column "expected results" in following table rather than the column "actual results"


Col A Col B Actual Result Expected result
3 4 $B$2 $B$2, $B$5, $B$6
5 3 $B$3 $B$3, $B$7
10 5 $B$4 $B$4
4 10 $B$1 $B$1
2 3 #N/A #N/A
1 3 #N/A #N/A
5 5 $B$4 $B$3, $B$7

As you will notice that the current formula gives cell reference for the first match, not the subsequent ones...

Hi and welcome to Mr Excel Forum

Your formula worked for me

3	4	$B$2
5	3	$B$3
10	5	$B$4
4	10	$B$1

<tbody>
</tbody>

M.

 

[confused_09]

Thanks for your reply.

I would like to have results as in the column "expected results" in following table rather than the column "actual results"

Col A	Col B	Actual Results	Expected results
3	4	$B$2	$B$2, $B$5, $B$6
5	3	$B$3	$B$3, $B$7
10	5	$B$4	$B$4
4	10	$B$1	$B$1
2	3	#N/A	#N/A
1	3	#N/A	#N/A
5	5	$B$3	$B$3, $B$7

<tbody>
</tbody>

As you will notice that the current formula gives cell reference for the first match, not the subsequent ones...

 

[Marcelo Branco]

Try this

Alt+F11 to open the VBEditor

go to Insert > Module

paste the code below (User Defined Function) in the right-panel

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Back to Excel

in D1 insert this array formula
=IF(COUNTIF($B$1:$B$1001,A1),SUBSTITUTE(Aconcat(IF($B$1:$B$1001=A1,", "&ADDRESS(ROW($B$1:$B$1001),2),"")),", ","",1),NA())

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

M.

 

[confused_09]

Thank you so much, i had almost given up on this.

I do not understand VBA/UDF, however would want to know what is this UDF doing here to spin this magic? It would be great if you can briefly explain what this UDF aconcat is doing?

Thanks a lot once more.

 

[Marcelo Branco]

Thank you so much, i had almost given up on this.

I do not understand VBA/UDF, however would want to know what is this UDF doing here to spin this magic? It would be great if you can briefly explain what this UDF aconcat is doing?

Thanks a lot once more.

You are very welcome and thanks for the feedback!

Excel has not a built-in function to concatenate values in an array or range.
The UDF aconcat does it!

In this specific situation an array is produced by this part of the formula
IF($B$1:$B$1001=A1,", "&ADDRESS(ROW($B$1:$B$1001),2),"")

For example, using your data sample in #4, for the first row, where A1=3, the array produced is - note the concatenation ", "&ADDRESS(ROW...)
{"";", $B$2";"";"";", $B$5";", $B$6";""}

Then the aconcat function concatenates these values producing the string
", $B$2, $B$5, $B$6"

At last, the SUBSTITUTE function substitutes the first instance of ", " for "" (empty string) resulting in
$B$2, $B$5, $B$6

That's it!

Hope i made myself clear.

M.