Ignore Index Variable

[akstiffney]

Hello, I have an excel sheet that uses an INDEX function with multiple criteria reference inputs and the function looks through a table of data and returns the rows that match the criteria inputs. I want to be able to ignore certain criteria if i want. I didn't know if a certain value could be entered into the reference inputs that would make the INDEX function ignore that variable. I hope that makes sense. I attached my formula below.

=IFERROR(INDEX('Total Data'!$A$9:$A$4581, SMALL(IF(1=((--('User Interface'!$B$13='Total Data'!$AD$9:$AD$4581))*(--('User Interface'!$B$7='Total Data'!$X$9:$X$4581))*(--('User Interface'!$B$10='Total Data'!$Z$9:$Z$4581))), ROW('Total Data'!$A$9:$A$4581)-8,""), ROW()-26)),"")

 

[pgc01]

Hi
Welcome to the board

Let's say that you use the string "NA" to ignore criteria (choose another code if you have values equal to "NA").

To ignore the first criterion write "NA" in 'User Interface'!$B$13 and replace

(--('User Interface'!$B$13='Total Data'!$AD$9:$AD$4581))

with

(('User Interface'!$B$13='Total Data'!$AD$9:$AD$4581)+('User Interface'!$B$13="NA"))

Same for the other criteria.

 

[akstiffney]

Thanks for the reply! I made the change you suggested. Now, if all the criteria are "NA" all of the data is returned by the INDEX function, which makes sense, but once I include a criteria the INDEX function returns no data.
If you have a fix for it let me know. Below is my new equation.

=IFERROR(INDEX('Total Data'!$A$9:$A$4581, SMALL(IF(1=((('User Interface'!$B$13='Total Data'!$AD$9:$AD$4581)+('User Interface'!$B$13="NA"))*(('User Interface'!$B$7='Total Data'!$AD$9:$AD$4581)+('User Interface'!$B$7="NA"))*(('User Interface'!$B$10='Total Data'!$AD$9:$AD$4581)+('User Interface'!$B$10="NA"))), ROW('Total Data'!$A$9:$A$4581)-8,""), ROW()-26)),"")

 

[pgc01]

Hi

In this last formula you are just using column AD.

In your first post you use AD, X and Z.

Please check.

 

[akstiffney]

Ahhh duh, now it works perfect! Thank you so much!!

 

[pgc01]

You're welcome. Thanks for the feedback.