Home

IF cell = 0 then add cell name to list

Matthew Mullin
New Contributor

Hello all, 

I have created a spreadsheet based on Magic the Gathering, so far its all good and working as it should. What I'm struggling with is this..... I have card name in B3 and the number of cards I have in D3, now if I didn't have the card then the value of D3 would be 0.  What I would like is a list/Column of the cards that I don't yet have and this would update as the values change etc. 

 

So I could be overlooking this or over complicating it but I can't figure it out. 

I'm guessing an IF but after that I'm not sure.

 

Any help would be great!

5 Replies

It can be done with an advanced version of index match.

 

The formula looks something like this:

=INDEX(cards[Card],SMALL(IF(cards['# Owned]=0,ROW(cards['# Owned])-ROW(INDEX(cards['# Owned],1,1))+1),ROW(A1)))

 

and uses an array function to build a list of all the cards where you have 0 owned, then uses the small function to return the nth result in that list. In this case i've used row(a1) as the second parameter in the small so that when you drag it down it increments and gives you the next result.

 

Have a look at the attached spreadsheet. Because this is an array formula you need to enter it with ctrl+shift+enter to make it work

To avoid array formula that could be

=IFERROR(INDEX(cards[Card],AGGREGATE(15,6,1/(cards['# Owned]=0)/NOT(ISBLANK(cards['# Owned]))*ROW(cards[Card])-ROW(cards[[#Headers],[Card]]),ROW(A1))),"")

and attached

 

Thank's, that's exactly what I was trying to do.

Alas I can't get it to work with my spreadsheet, could it be to do with my layout? 

 

I have used the formula as you typed it only with changes that make it relevant to my sheet.

I've attached a snippet of the sheet if you wouldn't mind having a look?

Matthew, attached is variant with non-array formula

=IFERROR(INDEX($C$4:$C$21,AGGREGATE(15,6,1/($E$4:$E$21=0)*(ROW($B$4:$B$21)-ROW($B$3)),ROW($B4)-ROW($B$3))),"")

 

Thank you so much! 

 

I see where I was going wrong now. I was calling the wrong ROW.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies