MixedUpExcel
Board Regular
- Joined
- Apr 7, 2015
- Messages
- 222
- Office Version
- 365
- Platform
- Windows
Hi All,
Hope this is easy for someone as I've not been able to come up with a solution.
I need to use Index Match Match to find a Match to a name in Column A and then to find the first cell that has a value in Column B (and then to return that value from Column B).
<tbody>
</tbody>
I have found that if I use
it will return 123
The problem I have is that where it says Mark, I don't actually know what the data will be in that cell - so I need it to find the first non-blank cell and return the value (may be a number or text) which is in that cell (in Column B)
I can use this
but it only takes into account Column B and I have thousands of names in Column A and if I was to put Craig instead of Simon - I would want it to return the value of 85 because that is the first Craig with another name next to it in Column B.
Recap - even though I've put values in Column C - these are just for testing - I want to be able to match a name in Column A and find the first non-blank name in Column B and return that value from Column B.
Is there a simple way to do this please?
Thanks.
Simon
Hope this is easy for someone as I've not been able to come up with a solution.
I need to use Index Match Match to find a Match to a name in Column A and then to find the first cell that has a value in Column B (and then to return that value from Column B).
A | B | C | E | F | G | |
Simon | Mark | Simon | Formula | |||
John | ||||||
Simon | ||||||
Paul | ||||||
Simon | Mark | 123 | ||||
Simon | ||||||
Simon | ||||||
Craig | James | 85 | ||||
Simon | Daniel | 564 |
<tbody>
</tbody>
I have found that if I use
Code:
=INDEX($C$2:$C$10,MATCH(1,(F2=$A$2:$A$10)*(E2=$B$2:$B$10),0))
The problem I have is that where it says Mark, I don't actually know what the data will be in that cell - so I need it to find the first non-blank cell and return the value (may be a number or text) which is in that cell (in Column B)
I can use this
Code:
=INDEX($C$2:$C$10,MATCH(FALSE,ISBLANK($B$2:$B$10),0))
Recap - even though I've put values in Column C - these are just for testing - I want to be able to match a name in Column A and find the first non-blank name in Column B and return that value from Column B.
Is there a simple way to do this please?
Thanks.
Simon