# Thread: INDEX, MATCH, VLOOKUP to Exclude

1. ## INDEX, MATCH, VLOOKUP to Exclude

Hi
I need to a formula to display any of the weeks total's that are greater than zero and excluding week 5. Is there a way to make a vlookup or an index/match to exclude criteria/data?
 PERSON WEEK TOTAL MRS.TWO 5 20 MR.ONE 5 40 MR.ONE 26 0 MR.THR 3 5 MR.THR 5 40 MR.R 5 10 MR.R 1 3 MRS.FOU 4 30 MRS.FOU 5 40 MR.SEVE 5 40 MRS.TEN 5 40 MRS.TEN 20 2 MR.ELEV 3 4 MR.ELEV 5 25
Thank You
MZing81  Reply With Quote

2. Originally Posted by MZING81 Hi
I need to a formula to display any of the weeks total's that are greater than zero and excluding week 5. Is there a way to make a vlookup or an index/match to exclude criteria/data?
 PERSON WEEK TOTAL MRS.TWO 5 20 MR.ONE 5 40 MR.ONE 26 0 MR.THR 3 5 MR.THR 5 40 MR.R 5 10 MR.R 1 3 MRS.FOU 4 30 MRS.FOU 5 40 MR.SEVE 5 40 MRS.TEN 5 40 MRS.TEN 20 2 MR.ELEV 3 4 MR.ELEV 5 25
Thank You
MZing81
The idea is not to use a pivot table seeing that this already is one. I want a formula for it. It's going to be part of larger project with a generator. I have already put together an index and match formula with this table to pull data based on criteria from two columns. But the formula will me avoid using a autofilter in a macro which thru experience has been problematic for me.  Reply With Quote

3. Assuming that your table starts in A1, and the name of your first subject is in G2:

=SUMPRODUCT(--(\$A\$2:\$A\$15=G2),--(\$B\$2:\$B\$15<>5),\$C\$2:\$C\$15)  Reply With Quote

4. Originally Posted by Ken Puls Assuming that your table starts in A1, and the name of your first subject is in G2:

=SUMPRODUCT(--(\$A\$2:\$A\$15=G2),--(\$B\$2:\$B\$15<>5),\$C\$2:\$C\$15)
Gave it a try and don't think that it worked was intended only got "0". Maybe I'm missing something. By subject in g2 do you mean the the week number being 5 in column G or the actual employee name? Gave it a try with the employee list in column G but the totals arent accurate.

Thanks for the help  Reply With Quote

5. Hi there,

I've attached a sample workbook. Tell me if that's what you were after.  Reply With Quote

6. You could use these two array formulae

=IF(ISERROR(SMALL(IF(\$B\$2:\$B\$15<>5,ROW(\$A\$2:\$A\$15)),ROW(A1))),"",
INDEX(\$A\$2:\$A\$15,SMALL(IF(\$B\$2:\$B\$15<>5,ROW(\$A\$2:\$A\$15)),ROW(A1))-1))

and

=IF(ISERROR(SMALL(IF(\$B\$2:\$B\$15<>5,ROW(\$A\$2:\$A\$15)),ROW(A1))),"",
INDEX(\$C\$2:\$C\$15,SMALL(IF(\$B\$2:\$B\$15<>5,ROW(\$A\$2:\$A\$15)),ROW(A1))-1))

The first gets the name where week <> 5, and the second gets the total. Copy down as far as you think you might need.  Reply With Quote

#### Posting Permissions

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