Match excluding duplicates

[fastballfreddy]

I'm trying to match two columns of data while not matching the same value twice. the match formula just finds the first value. the below example when matching data 2 the 5 always matches the first 5 in Data 1. Is there a formula so once the first 5 is matched it will match it with the 2nd one and so on?

Data 1

Data 2

Match Formula

5	20	3
25	5	1
20	5	1
5	45	#N/A
15	5	1
5	5	1
5	50	#N/A

 

[awoohaw]

are you asking for the row location of the subsequent numbers?
if so, try this:

Book1
	A	B	C	D
1	Data 1	Data 2
2	5	20		3
3	25	5		1
4	20	5		4
5	5	45		Not Found
6	15	5		6
7	5	5		7
8	5	50		Not Found
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=IFERROR(1/(LARGE(IFERROR(1/(($A$2:$A$8=$B2)*(ROW($A$2:$A$8)-ROW($A$2)+1)),0),COUNTIF($B$2:$B2,B2))),"Not Found")

 

[DanteAmor]

Try:

Libro1
	A	B	C
1	Data 1	Data 2
2	5	20	3
3	25	5	1
4	20	5	4
5	5	45	Not Found
6	15	5	6
7	5	5	7
8	5	50	Not Found
Hoja1

Cell Formulas
Range	Formula
C2:C8	C2	=IFERROR(SMALL(IF($A$2:$A$8=B2,ROW($A$2:$A$8)),COUNTIF($B$2:B2,B2))-ROW($B$2)+ROW($B$2)-1,"Not Found")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kvsrinivasamurthy]

In C2 copied down.

=IFERROR(AGGREGATE(15,6,ROW($A$2:$A$8)/($A$2:$A$8=$B2),COUNTIF($B$2:$B2,$B2))-ROW($A$1),"")

Data 1	Data 2	FORMULA	Match Formula
5​	20​	3	3​
25​	5​	1	1​
20​	5​	4	1​
5​	45​		#N/A​
15​	5​	6	1​
5​	5​	7	1​
5​	50​		#N/A​

 

[fastballfreddy]

Thanks for all of the answers! I tried Dante's and it works great! Thanks again for everyone's responses and help!