Return first non blank result based on lookup value using INDEX or MATCH or LOOKUP

[Dan1980]

I think I've got a pretty simple one to solve but I can't seem to find the answer within the mrexcel forum.

I want to get the Suburb entries of particular customers.

The INDEX and MATCH combination works well until I get to a blank entry, this is the formula I have been using, simplified for here:

	A	B
1	Customer	Suburb
2	John Smith	Croydon
3	Simon Crouch
4	Simon Crouch	Concord
5	Jane Doe	Marrickville
6	Sam White	Parramatta

<tbody>
</tbody>

To get John Smith's Suburb: -

=INDEX(B2:B6,MATCH(A2,A2:A6,0))

=Croydon

As soon as I want to get Simon Crouch's Suburb: -

=INDEX(B2:B6,MATCH(A4,A2:A6,0))

=0

In essence there are thousands of customers, many of which are entered more than once, but there is at least one Suburb entry for each customer.
How do I get excel to display the first non blank Suburb entry for a specific Customer?

Regards,

Dan1980

 

[FDibbins]

The reason you get 0 is because the 1st match has nothing in column B.

INDEX/MATCH and VLOOKUP always ONLY find the 1st match, then they stop looking.

 

[MarcelBeug]

Try:

=LOOKUP(2,1/(INDEX(($A$2:$A$6=A4)*($B$2:$B$6<>""),0)),$B$2:$B$6)

 

[Dan1980]

Try:

=LOOKUP(2,1/(INDEX(($A$2:$A$6=A4)*($B$2:$B$6<>""),0)),$B$2:$B$6)

You are an Excel genius. It works beautifully. Thank you so much.

 

[MarcelBeug]

You're welcome.

 

[benntw]

Marcel, I am curious about your formula. This works perfect for a similar problem I was having. Just curious what 2,1 represents ? I used your formula and instead of looking at B range I was looking at Z range and it still worked perfect. If I am understanding it correctly 2 would reference column B and 1 would reference column A. When I changed the 2 to 26 it still had the same result. I was just making sure I understood your formula correctly. Thanks

 

[Marcelo Branco]

Marcel, I am curious about your formula. This works perfect for a similar problem I was having. Just curious what 2,1 represents ? I used your formula and instead of looking at B range I was looking at Z range and it still worked perfect. If I am understanding it correctly 2 would reference column B and 1 would reference column A. When I changed the 2 to 26 it still had the same result. I was just making sure I understood your formula correctly. Thanks

See the excellent explanation provided by Aladin Akyurek at
https://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425

M.