I have a list below and want to return all matching results in column B into column D. Also, I wanted to copy down the formula in column E to return all the results against every name that matches. The length of the lists do not match and there is a blank column but this does not matter as it a real life scenario I am trying to replicate. I have tried INDEX and INDEX MATCH combinations but all the examples only look for one return value. Any help is appreciated to use the correct formula in cell E2 to then copy down for all names and results to match. Many thanks
A | B | C | D | E | ||
1 | Name | Result | Name | Result | ||
2 | John | Pass | Davinder | |||
3 | Mark | Fail | Mary | |||
4 | Davinder | Fail | Helen | |||
5 | Ola | Pass | Ola | |||
6 | Tom | Pass | Tom | |||
7 | Helen | Pass | Mark | |||
8 | John | |||||
9 | Luke |