VLOOKUP won't help

InactiveB1208B · Feb 15, 2011

Hi,

I'm new here, but I subscribe to the podcasts and have been a big fan for a while.

I have a workbook with 4 columns. The column A has names, and column D had either "yes" or "no". From this, I want to create a list of just those names that have a "yes" in column D. There are many "yes" entries. How can I do this without using a macro or VB?

Thanks.

MrKowz · Feb 15, 2011

Try using Autofilter or a PivotTable (with the yes/no in row field and names in data field)

InactiveB1208B · Feb 15, 2011

that's one idea, but I was looking to have it automatically populat a list at the bottom, and having a pivot table wouldn't look right and would require an extra step in between. I want the list to update as I go.

hiker95 · Feb 15, 2011

elihanover,

Excel Workbook

A

B

C

D

E

F

G

1

Names

yes / no

yes

3

2

A

yes

A

3

B

no

E

4

C

no

I

5

D

no

6

E

yes

7

F

no

8

G

no

9

H

no

10

I

yes

11

Sheet1

Copy the following array formula into cell F2, confirmed with CTRL + SHIFT + ENTER, not just ENTER, and then copy it down until the cell displayes nothing.

=IF(ISERROR(INDEX($A$2:$A$100,SMALL(IF($D$2:$D$100=$F$1,ROW($A$2:$A$100)-ROW($F$2)+1),ROWS($D$2:D2)))),"",INDEX($A$2:$A$100,SMALL(IF($D$2:$D$100=$F$1,ROW($A$2:$A$100)-ROW($F$2)+1),ROWS($D$2:D2))))

The formula in cell G1 tells you how many times to copy the array formula.

InactiveB1208B · Feb 15, 2011

Great! Can you explain to me what each step in that formula is doing conceptually?

Thanks again.

Aladin Akyurek · Feb 15, 2011

hiker95 said:
elihanover,

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 76px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 82px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Names</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">yes / no</TD><TD> </TD><TD style="TEXT-ALIGN: center">yes</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>A</TD><TD> </TD><TD> </TD><TD>yes</TD><TD> </TD><TD style="FONT-FAMILY: Verdana">A</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>B</TD><TD> </TD><TD> </TD><TD>no</TD><TD> </TD><TD style="FONT-FAMILY: Verdana">E</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>C</TD><TD> </TD><TD> </TD><TD>no</TD><TD> </TD><TD style="FONT-FAMILY: Verdana">I</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>D</TD><TD> </TD><TD> </TD><TD>no</TD><TD> </TD><TD style="FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>E</TD><TD> </TD><TD> </TD><TD>yes</TD><TD> </TD><TD style="FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>F</TD><TD> </TD><TD> </TD><TD>no</TD><TD> </TD><TD style="FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>G</TD><TD> </TD><TD> </TD><TD>no</TD><TD> </TD><TD style="FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>H</TD><TD> </TD><TD> </TD><TD>no</TD><TD> </TD><TD style="FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>I</TD><TD> </TD><TD> </TD><TD>yes</TD><TD> </TD><TD style="FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>...

G1:

Code:

=COUNTIF($D$2:$D$10,F1)

F2, control+shift+enter, not just enter, and copy down:

Code:

=IF(ROWS($F$2:F2)<=$G$1,INDEX($A$2:$A$10,
   SMALL(IF($D$2:$D$10=$F$1,ROW($D$2:$D$10)-ROW($D$2)+1),
    ROWS($F$2:F2))),"")

This set up is far less expensive. Put othwerwise: It's faster.

hiker95 · Feb 15, 2011

Aladin Akyurek,

Thank you very much - what a great place to learn something new.

VLOOKUP won't help

InactiveB1208B

New Member

Excel Facts

MrKowz

Well-known Member

InactiveB1208B

New Member

hiker95

Well-known Member

InactiveB1208B

New Member

Aladin Akyurek

MrExcel MVP

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

VLOOKUP won't help

InactiveB1208B

New Member

Excel Facts

MrKowz

Well-known Member

InactiveB1208B

New Member

hiker95

Well-known Member

InactiveB1208B

New Member

Aladin Akyurek

MrExcel MVP

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock