lookup

[pgc]

help please i want a cell to display the first duplicate number in two
lists for example
a1 a2 a3
1 3 5
3 2
5 5
7 7
9

i want a3 to show 5 but not count subsequent doubles

 

[Bernie Deitrick]

Paul,

I'm going to guess that you actually want a formula for cell C1.

Array enter (enter using Ctrl-Shift-Enter)

=INDEX(A:A,MIN(IF(A1:A10=B1:B10,ROW(A1:A10),13)))

In Cell A13, enter "No match found"

Change the A1:A10 and B1:B10 to reflect the addresses of your actual table where you want to look
for matches, and change the 13 to a row below the table (and enter the "No match found" in that
cell")

HTH,
Bernie
MS Excel MVP

 

[pgc]

That works for matching in 2 different columns how about
A1 B1 C1
1 2 1
1 3
3 3
C1 returns 1 because it is the first number duplicated in either and
both columns

been on holiday sorry for delay in reply
thank you very much