I am showing someone how to extract common records between 2 lists on Excel 2019. I've copied this formula from the Web but I cannot get it to calculate correctly. Would like to know what is wrong with it and if there's a simpler formula that would work in this Excel version.
| Book10 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ||||||||
| 2 | List 1 | List 2 | Both lists | |||||
| 3 | Jack | Ted | 0 | |||||
| 4 | Ted | Tom | ||||||
| 5 | Peter | Vic | ||||||
| 6 | Fred | Jane | ||||||
| 7 | Sally | Fred | ||||||
| 8 | Nel | |||||||
Sheet2 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F3 | F3 | =INDEX(Values1,SMALL(IF(COUNTIF(Values2,Values1)*NOT(COUNTIF($F$3:F3,Values1)), ROW(Values1)-MIN(ROW(Values1))+1,""),1)) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| Values1 | =Sheet2!$B$3:$B$8 | F3 |
| Values2 | =Sheet2!$D$3:$D$7 | F3 |
