INDEX, MATCH, VLOOKUP to Exclude

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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
 
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.
 
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)
 
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
 
Last edited:
Hi there,

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

Attachments

  • xlgf850-1.xlsx
    9.5 KB · Views: 235
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.
 
Back
Top