Index Match Match - 2nd Match to find non-blank cell

[MixedUpExcel]

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).

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

=INDEX($C$2:$C$10,MATCH(1,(F2=$A$2:$A$10)*(E2=$B$2:$B$10),0))

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

=INDEX($C$2:$C$10,MATCH(FALSE,ISBLANK($B$2:$B$10),0))

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

 

[MixedUpExcel]

Well I've got this to work by confirming it with CTRL+SHIFT+ENTER

=INDEX($B$2:$B$10,MATCH(1,(F4=A3:A11)*(FALSE=ISBLANK($B$2:$B$10)),0))

in case it's of use to anyone else

Thanks.