# Formula to list missing values

#### Brew

##### Well-known Member
How do i create a formula or macro that will list in the target range ck5:ck13 all values from the list in BW1:CF1, which are not listed in BW5:CF18

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Domenic

##### MrExcel MVP
Try the following...

CK5, copied down:

=INDEX(\$BW\$1:\$CF\$1,SMALL(IF(COUNTIF(\$BW\$5:\$CF\$18,\$BW\$1:\$CF\$1)=0,COLUMN(\$BW\$1:\$CF\$1)-COLUMN(\$BW\$1)+1),ROWS(\$CK\$5:CK5)))

...confirmed with CONTROL+SHIFT+ENTER.

To include an error trap...

CJ5:

=SUMPRODUCT(--(COUNTIF(BW5:CF18,BW1:CF1)=0))

CK5, copied down:

=IF(ROWS(\$CK\$5:CK5)<=\$CJ\$5,INDEX(\$BW\$1:\$CF\$1,SMALL(IF(COUNTIF(\$BW\$5:\$CF\$18,\$BW\$1:\$CF\$1)=0,COLUMN(\$BW\$1:\$CF\$1)-COLUMN(\$BW\$1)+1),ROWS(\$CK\$5:CK5))),"")

...confirmed with CONTROL+SHIFT+ENTER. If you prefer to have one formula, try the following instead...

=IF(ROWS(\$CK\$5:CK5)<=SUMPRODUCT(--(COUNTIF(\$BW\$5:\$CF\$18,\$BW\$1:\$CF\$1)=0)),INDEX(\$BW\$1:\$CF\$1,SMALL(IF(COUNTIF(\$BW\$5:\$CF\$18,\$BW\$1:\$CF\$1)=0,COLUMN(\$BW\$1:\$CF\$1)-COLUMN(\$BW\$1)+1),ROWS(\$CK\$5:CK5))),"")

...confirmed with CONTROL+SHIFT+ENTER. Note that having it in one formula is not as effficient.

Hope this helps!

#### Brew

##### Well-known Member
Thanks Domenic, that works perfectly!!!

Replies
11
Views
158
Replies
15
Views
193
Replies
3
Views
75
Replies
10
Views
159
Replies
5
Views
82