Forum Discussion

packie's avatar
packie
Brass Contributor
Jul 08, 2024

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

  • 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 

    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.

Resources