I'm trying to exclude a single result from INDEX listing. For example let's say that INDEX gives me 10 columns ( B1:10 ) with the numbers 0-9, I have a function in A1 that gives out number between 0 and 9 ( depending on changes up the chain ) and I want to exclude that specific number from showing up in the INDEX list.

I don't know if it makes any difference, but in my real case I'm dealing with plain text rather than numbers.


This can be done with query as follows:

 =query({index(B1:Z10, 2, 3)}, "select * where Col1 <> '"&A1&"'", 0)

The query string means "select all where column 1 of the queried array is not equal to the content of cell A1". The curly braces around index are there to make it possible to refer to the column as "Col1", without knowing whether it's B,C,D, or something else depending on column offset. The last parameter 0 says there is no header row in the queried data.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.