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:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Customer[/TD]
[TD]Suburb[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John Smith[/TD]
[TD]Croydon[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Simon Crouch[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Simon Crouch[/TD]
[TD]Concord[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jane Doe[/TD]
[TD]Marrickville[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sam White[/TD]
[TD]Parramatta[/TD]
[/TR]
</tbody>[/TABLE]
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
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Customer[/TD]
[TD]Suburb[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John Smith[/TD]
[TD]Croydon[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Simon Crouch[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Simon Crouch[/TD]
[TD]Concord[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jane Doe[/TD]
[TD]Marrickville[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sam White[/TD]
[TD]Parramatta[/TD]
[/TR]
</tbody>[/TABLE]
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