SOLVED

# Return value within a range if duplicate is found

Brass Contributor

# Return value within a range if duplicate is found

The sample spreadsheet demonstrates what I am trying to achieve.

E3:E12  contains values that contain a single and duplicate records

F3:F12 contains a unique value in some cells

G3:G12 is where the formula goes

The formula looks for a unique value in F3:F12 and then looks in E3:E12 to find a duplicate for that record. The result should only be returned for those cells that has an empty cell in F3:F12

The sample spreadsheet should be clearer that how I am explaining it here.

CarCodeDuplicates.xlsx

2 Replies
best response confirmed by packie (Brass Contributor)
Solution

# Re: Return value within a range if duplicate is found

In G3:

=IFERROR(IF(F3="", INDEX(\$F\$3:\$F\$12, MATCH(1, (\$E\$3:\$E\$12=E3)*(\$F\$3:\$F\$12<>""), 0)), ""), "")

If you do not have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.

Then fill down.

# Re: Return value within a range if duplicate is found

As variant

``=IF(F3="", INDEX(\$F\$3:\$F\$12, MATCH(1,  INDEX( (\$E\$3:\$E\$12=E3)*(\$F\$3:\$F\$12<>""), 0), 0) ), "" )``

and drag it down

1 best response

Accepted Solutions
best response confirmed by packie (Brass Contributor)
Solution

# Re: Return value within a range if duplicate is found

In G3:

=IFERROR(IF(F3="", INDEX(\$F\$3:\$F\$12, MATCH(1, (\$E\$3:\$E\$12=E3)*(\$F\$3:\$F\$12<>""), 0)), ""), "")

If you do not have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.

Then fill down.