# INDEX MATCH formula with multiple ctriteria - not getting it right...

1. ## INDEX MATCH formula with multiple ctriteria - not getting it right...

Hi,

New here and new to slightly more complex Formulas in Excel, I keep struggling with following task.

I need to retrieve all cells / values from column A for which there is data >0 in at least one of the columns B, C and D.
Rows can look as follows:
(A) (B) (C) (D)
ExA 0 0 6
ExB 5 5 0
ExC 0 0 0
ExD 1 8 2
ExE 0 0 0
and so on
For above example I would like to retrieve ExA, ExB, ExD. Not ExC and ExE since they have 0 in all columns.

Have tried some combinations of INDEX and MATCH using OR and IF functions but I can't seem to get it right...

What I have tried (examples):
=INDEX(\$A\$4:\$A\$50; MATCH(0; OR(\$B\$4:\$B\$50=0;\$C\$4:\$C\$50=0;\$D\$4:\$D\$50=0)))
=INDEX(\$A\$4:\$A\$50; MATCH(0; IF(\$B\$4:\$B\$50<>0+\$C\$4:\$C\$50<>0+\$D\$4:\$D\$50<>0; 0; 1); 0))
=INDEX(\$A\$4:\$A\$50; MATCH(0; IF(OR(\$B\$4:\$B\$50<>0;\$C\$4:\$C\$50<>0;\$D\$4:\$D\$50<>0); 0; 1); 0))

Thanks!!
W.

2. ## Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

I don't get it. Do you want all the retrieved results in one cell?

3. ## Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

Ok, here is a start

4. ## Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

Based on what you have tried,
Formula:
`Please Login or Register  to view this content.`

5. ## Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

Yeah, I fixed it with inspiration from both your answers (working with sum)!
Thanks a million to both of you for quick and helpful replies!

6. ## Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

7. ## Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

Done, thanks for the comment AliGW! :-)

8. ## Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

I have a similar problem, trying to have an index and match formula that looks up 2 columns and 1 row. Not having any luck. Any help will be greatly appreciated.

9. ## Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1