Uninterrupted list with INDEX/MATCH

[dafan]

Hi all,

I have a list in which I have to lookup various Groups and find a Name accordingly. I am now using a very simple IF formula but I get blanks too. Refer below for an example. (A and B are data table, D and E are lookup).

Excel Workbook
	A	B	C	D	E
1	Name	Group		Group	Name
2		A		A	0
3	Mike	A		A	Mike
4		A		A	0
5	Jason	A		A	Jason
Sheet1

I want to get an uninterrupted list of the values. How can I achieve this?

Ive been playing around with SMALL(IF( and stuff but just can't figure it out.

 

[dafan]

I'm asking this for a friend so can't answer 100% sure, but it could be there is B inbetween. He just wants to get rid of the '0' values (or empty with a decent escape) when there is not an empty row.

But for the sake of keeping it easy, I will just assume there is just 'A' in the entire column B.

And yes you are right about the resources, it's an alternative and i'll see what I use in the end.

 

[Peter_SSs]

I still don't have a good idea of what a larger section of the sheet might look like and what the conditions for adding a name to the list are, but it wouldn't be as straight forward as this would it?

Excel Workbook
	A	B	C	D	E
1	Name	Group		Group	Name
2		A		A	Mike
3	Mike	A		A	Jason
4		A		A
5	Jason	A		A
6
Sheet1

 

[dafan]

No because Jason could be in A4 and then the formula doesnt work anymore.

I don't have a bigger part of the sheet, sorry.
I just need a lookup for multiple hits thats skips blanks.

 

[dafan]

Peter got feedback!
First example hit the nail right on the head, so no need to look any further.

Appreciate the help!, thanks!

 

[Aladin Akyurek]

Hi all,

I have a list in which I have to lookup various Groups and find a Name accordingly. I am now using a very simple IF formula but I get blanks too. Refer below for an example. (A and B are data table, D and E are lookup).

http://www.excel-jeanie-html.de/index.php?f=1
...


I want to get an uninterrupted list of the values. How can I achieve this?

Ive been playing around with SMALL(IF( and stuff but just can't figure it out.

A2:B6 houses the data, including the headers.

D:E houses the processing.

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=320 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>A</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:fmla='=SUM(IF(FREQUENCY(IF(A3:A6<>"",IF(B3:B6=D1,MATCH("~"&A3:A6,A3:A6&"",0))),ROW(A3:A6)-ROW(A3)+1),1))' x:num x:arrayrange="E1">2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17>Name</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Group</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Group</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Name</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>A</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS($D$3:D3)<=$E$1,$D$1,"")'>A</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS($E$3:E3)<=$E$1,INDEX($A$3:$A$6,SMALL(IF($B$3:$B$6=$D$1,IF($A$3:$A$6<>"",ROW($A$3:$A$6)-ROW($A$3)+1)),ROWS($E$3:E3))),"")' x:arrayrange="E3">Mike</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17>Mike</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>A</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS($D$3:D4)<=$E$1,$D$1,"")'>A</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS($E$3:E4)<=$E$1,INDEX($A$3:$A$6,SMALL(IF($B$3:$B$6=$D$1,IF($A$3:$A$6<>"",ROW($A$3:$A$6)-ROW($A$3)+1)),ROWS($E$3:E4))),"")' x:arrayrange="E4">Jason</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>A</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS($D$3:D5)<=$E$1,$D$1,"")'> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS($E$3:E5)<=$E$1,INDEX($A$3:$A$6,SMALL(IF($B$3:$B$6=$D$1,IF($A$3:$A$6<>"",ROW($A$3:$A$6)-ROW($A$3)+1)),ROWS($E$3:E5))),"")' x:arrayrange="E5"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>Jason</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>A</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS($D$3:D6)<=$E$1,$D$1,"")'> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS($E$3:E6)<=$E$1,INDEX($A$3:$A$6,SMALL(IF($B$3:$B$6=$D$1,IF($A$3:$A$6<>"",ROW($A$3:$A$6)-ROW($A$3)+1)),ROWS($E$3:E6))),"")' x:arrayrange="E6"> </TD></TR></TBODY></TABLE>

D1: A

which houses the desired group id.

E1:

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(
    IF(A3:A6<>"",
    IF(B3:B6=D1,MATCH("~"&A3:A6,A3:A6&"",0))),
     ROW(A3:A6)-ROW(A3)+1),1))

D3, copy down:

=IF(ROWS($D$3:D3)<=$E$1,$D$1,"")

E3:

Control+shift+enter, not just enter...

=IF(ROWS($E$3:E3)<=$E$1,
   INDEX($A$3:$A$6,SMALL(
    IF($B$3:$B$6=$D$1,
    IF($A$3:$A$6<>"",ROW($A$3:$A$6)-ROW($A$3)+1)),
     ROWS($E$3:E3))),"")

...and copy down.