I am doing a vlookup which is working fine. But vlookup only returns the 1st occurrence of the lookup value. My array has more than one occurrence of the lookup value, and I need it to locate all such occurrences, not just the 1st one.
Eg. col A has John, Ted, Peter, John, Fred, John (going downwards)
col B has 7,9,4,8,5,2
In cell D1, I need Excel to find the 1st occurrence of John's name and return the value 7.
In cell E1 I need it to find the 2nd occurrence and return the value 8.
Cell F1 needs to end up with the value 2.
If there are more occurrences, they need to go in cells G1, H1 etc. This needs to happen for anything up to 20 times.
How can I get Excel to do this? Any help would be really appreciated!
Eg. col A has John, Ted, Peter, John, Fred, John (going downwards)
col B has 7,9,4,8,5,2
In cell D1, I need Excel to find the 1st occurrence of John's name and return the value 7.
In cell E1 I need it to find the 2nd occurrence and return the value 8.
Cell F1 needs to end up with the value 2.
If there are more occurrences, they need to go in cells G1, H1 etc. This needs to happen for anything up to 20 times.
How can I get Excel to do this? Any help would be really appreciated!
