SOLVED

Return value within a range if duplicate is found

Brass Contributor

 

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

@packie 

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.

@packie 

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

@packie 

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.

View solution in original post