I’m really struggling to find an answer to this as online I’ve really only found VBA solutions to this problem which isn’t what I wish to learn how to do.

**THE PROBLEM**

```
BLOOD NAME AGE GENDER
A David 18 Male
B Sarah 22 Female
O Lucy 32 Female
AB Steven 23 Male
O John 11 Male
B Mike 25 Male
AB Paul 24 Male
O Amy 23 Female
B Drake 22 Female
O Linda 11 Female
```

Very simply from the above dataset I wish to recreate this range but filter for only select BLOOD TYPE O.

**MY ATTEMPTS**

Started with a VLookup table however that stops at the first occurrence of O. Then tried incorporating IF/THEN/ELSE logic into a MATCH operand trying to locate the row numbers outputting to an array. (not gonna post my failed attempts) I did find a similarish problem online however they solved it via referencing the range manually using ROW(A1), ROW(A2) etc etc wasn’t what I after.

Really want to learn how to do this type of iterative selections using Excel formulae only. Even if not solving the problem any direction towards resources where I can learn more about this type problem, would be still appreciated.

This does not use array formulas, but does use a *helper column*. Assuming data in cols **A** through **D**, in **E2** enter:

```
=IF(A2="O",1+MAX($E$1:E1),"")
```

and copy down:

Each of the **O** rows is marked with a simple sequential value. This makes it easy for the usual *MATCH() / INDEX()* methods.

Pick some other cell and enter:

```
=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),$E:$E,0)),"")
```

and copy this cell both across and down:

### Answer：

Here is a solution with array formulas. It will calculate extremely slowly, and honestly VBA is a much better solution. You will need to tell excel these are array formulas by hitting “Ctrl + Shift + Enter” after inputting the formulas, this will add the {} around the equation. Finally, drag down the array formulas to see the first “X” results with blood type “O”:

First cell formula for “Blood” –> assumes blood is in column A of sheet1

`{=IFERROR(INDEX(Sheet1!$A:$D,SMALL(IF(Sheet1!$A:$A="O",ROW(Sheet1!$A:$A)),ROW(1:1)),1,1),"")}`

First cell formula for “Name” –> assumes name is in column B of sheet1

`{=IFERROR(INDEX(Sheet1!$A:$D,SMALL(IF(Sheet1!$A:$A="O",ROW(Sheet1!$A:$A)),ROW(1:1)),2,1),"")}`

First cell formula for “Age” –> assumes age is in column c of sheet1

`{=IFERROR(INDEX(Sheet1!$A:$D,SMALL(IF(Sheet1!$A:$A="O",ROW(Sheet1!$A:$A)),ROW(1:1)),3,1),"")}`

First cell formula for “Gender” –> assumes gender is in column d of sheet1

`{=IFERROR(INDEX(Sheet1!$A:$D,SMALL(IF(Sheet1!$A:$A="O",ROW(Sheet1!$A:$A)),ROW(1:1)),4,1),"")}`

Results:

```
BLOOD NAME AGE GENDER
O Lucy 32 Female
O John 11 Male
O Amy 23 Female
O Linda 11 Female
```

### Answer：

The following array formula can be put in row 2 (anywhere from column E onward) and copied across 3 columns and down as far as is necessary:

`=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("O",$A$2:$A$11)),ROW($A$2:$A$11),""),ROW()-1)),"")`

This is entered using `Ctrl` + `Shift` + `Enter` and uses a fixed array (A2:A11). If your array is going to change size, you can make the reference to it dynamic by using `INDIRECT`

and `COUNTA`

so that it always encompasses the *used* range, like so:

`=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("O",INDIRECT("$A2:$A"&COUNTA(A:A)))),ROW(INDIRECT("$A2:$A"&COUNTA(A:A))),""),ROW()-1)),"")`

**What is happening:**

The `SEARCH`

function is looking for “O”s, then the `IF`

returns the row number if an “O” was found and nothing if no “O” was found.

The `SMALL`

function is looking for the *n*th instance of the results returned by the `SEARCH`

function, where *n* = `ROW()-1`

.

The `INDEX`

function returns the *n*th value from the array A:A, B:B, etc, where *n* = the row number returned by the `SMALL`

function.

The `IFERROR`

function is not necessary but it makes for a cleaner dataset, all it does is replace the formulas that didn’t return anything useful with a blank instead.

Tags: excelexcel, sed, vba