Return Cell Address After Match

[ed9213]

This doesn't appear to be a new subject, but the solutions I've found don't seem to work for me.

I have a range of approximately 200 unique alphanumeric entries in column A.

I have new entries in column B and want to verify if there are any matching entries in column A.

The formula isn't a simple match; I want the result to point to the cell that has the match. So, near the entry in column B that is the specified item, if there is a match I would like to see the cell location in column A that holds it.

There must be a way to do this, but I can't seem to get this resolved by myself.

Thanks.

ed9213

 

[Fluff]

How about
=ADDRESS(MATCH(B2,A:A,0),1,4)

 

[sandy666]

[ deleted ] bad idea

 

[Momentman]

You mean something like this

Book1
	A	B	C
1	Abc123	abd347	$A$4
2	def345	cde123	$A$7
3	erf124
4	abd347
5	efr554
6	red2344
7	cde123
Sheet2

Cell Formulas
Range	Formula
C1	=ADDRESS(MATCH(B1,$A$1:$A$7,0),1)

 

[sandy666]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column A[/td][td=bgcolor:#5B9BD5]Column B[/td][td][/td][td=bgcolor:#70AD47]Column A[/td][td=bgcolor:#70AD47]Row[/td][td=bgcolor:#70AD47]Column B[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]abc[/td][td=bgcolor:#DDEBF7]555g[/td][td][/td][td=bgcolor:#E2EFDA]abc[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]abc[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ww1[/td][td]tgtg[/td][td][/td][td]555g[/td][td]

4​

[/td][td]555g[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]123x[/td][td=bgcolor:#DDEBF7]abc[/td][td][/td][td=bgcolor:#E2EFDA]ww1[/td][td=bgcolor:#E2EFDA]

2​

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

[tr=bgcolor:#FFFFFF][td]555g[/td][td]xxxx[/td][td][/td][td]123x[/td][td]

3​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]zxzxzx[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]zxzxzx[/td][td=bgcolor:#E2EFDA]

5​

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

[tr=bgcolor:#FFFFFF][td]dddd[/td][td][/td][td][/td][td]dddd[/td][td]

6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]tgtg[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]tgtg[/td][td=bgcolor:#E2EFDA]

7​

[/td][td=bgcolor:#E2EFDA]tgtg[/td][/tr]

[tr=bgcolor:#FFFFFF][td]qqqq[/td][td][/td][td][/td][td]qqqq[/td][td]

8​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]xxxx[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]xxxx[/td][td=bgcolor:#E2EFDA]

9​

[/td][td=bgcolor:#E2EFDA]xxxx[/td][/tr]

[tr=bgcolor:#FFFFFF][td]vvvv[/td][td][/td][td][/td][td]vvvv[/td][td]

10​

[/td][td][/td][/tr]
[/table]

PowerQuery:

M-code

[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"Column A"},Table2,{"Column B"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column B"}, {"Column B"})
in
    #"Expanded Table2"[/SIZE]

 

[ed9213]

You mean something like this

	A	B	C
Abc123	abd347	$A$4
def345	cde123	$A$7
erf124
abd347
efr554
red2344
cde123

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=ADDRESS(MATCH(B1,$A$1:$A$7,0),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Momentman,

Your formula is the closest to what I need. Is it possible to make it case-sensitive?

Thanks.
ed9213

 

[Fluff]

How about

Excel 2013/2016
	A	B	C
2	AL1 5EG	AL2 1UX	A3
3	AL10 9WX	AL3 6AD	A7
4	AL2 1UX	AL4 0xx	No Match
5	AL2 2EJ	AL5 5FG	A15
6	AL2 3XZ	AL5 5UN	No Match
7	AL2 3YT
8	AL3 6AD
9	AL3 6NZ
10	AL4 0AA
11	AL4 0BW
12	AL4 0XX
13	AL4 9XZ
14	AL4 9YL
15	AL5 2PR
16	AL5 5FG
17	AL5 5UG
18	al5 5un
19	AL6 0PB
20	AL6 0PE
21	AL6 9TU
Master

Cell Formulas
Range	Formula
C2	{=IFERROR(ADDRESS(MATCH(1,--EXACT(A$2:A$21,B2),0),1,4),"No Match")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ed9213]

How about

Excel 2013/2016

	A	B	C
AL1 5EG	AL2 1UX	A3
AL10 9WX	AL3 6AD	A7
AL2 1UX	AL4 0xx	No Match
AL2 2EJ	AL5 5FG	A15
AL2 3XZ	AL5 5UN	No Match
AL2 3YT
AL3 6AD
AL3 6NZ
AL4 0AA
AL4 0BW
AL4 0XX
AL4 9XZ
AL4 9YL
AL5 2PR
AL5 5FG
AL5 5UG
al5 5un
AL6 0PB
AL6 0PE
AL6 9TU

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Master​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=IFERROR(ADDRESS(MATCH(1,--EXACT(A$2:A$21,B2),0),1,4),"No Match")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Hi Fluff,

That works great. Thanks.

One question, though. The result is always one row off.

In C2 of your example, A7 is really A8. C5's result (A15) is really A16.

Is this an Excel quirk or is something in the formula?

Thanks, again.
ed9213

 

[Fluff]

Is this an Excel quirk or is something in the formula?

It's me not checking the results properly.
It should be
=IFERROR(ADDRESS(MATCH(1,--EXACT(A$2:A$21,B2),0)+1,1,4),"No Match")

Also please do not quote whole posts as it clutters up the thread.

 

[ed9213]

Fluff,

Thank you.

That's perfect.

I appreciate your help.

ed9213