Forum Discussion
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.
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.
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.
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