MAX / OFFSET formula question

[Jenny B.]

Good Morning,

I have a quick Excel question that I'm hoping someone will be able to answer
for me.

I'm working with an Excel sheet with 20 Categories on it (Column A1 Heading
is "Categories" and it extends from A2 : A21). The column directly next it
(B1) has the header "Fav" and extends again from B2 : B21 (contains numerical
values). I'm currently using the formula Max (B2:B21) to find the greatest
number. Next I use the formula Offset(B1,Match(I2,$B$1:$B$21,0)-1,-1,1,1 - -
to place the category where it should be for viewing purposes.

My question is - if I have two values that are the same in column B (meaning
2 amounts for say the value 7) - how do I get both categories to appear in my
Offset answer line? Currently, it will just display one even though it might
match two values. I'm in need of capturing each Category that has that
particular numerical value.

Thank you for your review and appreciate any thoughts you might have - Jenny
B.

 

[T. Valko]

Try this:

Entered as an array using the key combination of CTRL, SHIFT,ENTER (not just
ENTER):

=IF(ROWS($1:1)<=COUNTIF(B$2:B$21,I$2),INDEX(A$2:A$21,SMALL(IF(B$2:B$21=I$2,ROW(A$2:A$21)-MIN(ROW(A$2:A$21))+1),ROWS($1:1))),"")

Copy down until you get blanks

Biff

 

[Jenny B.]

Hi Biff,

Thank you so much for your reply, but I have yet another question.

Here is an outline of the 3 cells I'm using the formula for. When using the
MAX feature with my current formula (not the new array you have provided), I
get 78 for the MAX and the "Category" that would appear with that would be
"Job Satisfaction" (since there is only 1 MAX in this instance).

When I input your formula, I come up with the Every Category directly to the
Left in A:A. It appears instead of selecting just the categories that would
appear when the number in B2:B21 is the MAX, it is just selecting everything
instead to the Left. Am I doing something wrong, or should this have been
pasted in between certain rows?


Thank you again for all of you help - Jenny B.





Categories FAV UNFAV
Values 71 13
Innovation 48 26
Growth 59 18
Communications 61 21
Supervision 53 24
Performance Management 47 29
Feedback 50 23
Job Demands 48 33
Recognition 36 34
Commitment 76 9
Responsibility 53 22
Work Group/Team 65 15
Leadership 48 30
Resource Allocation 48 29
Career Progress 51 20
Customer Focus 35 30
Survey 47 25
Job Satisfaction 78 8
Satisfaction Index 60 23
Total Survey Composite 30 70

 

[T. Valko]

This issue has been resolved. The OP made a new post. They didn't enter the
formula as an array.

Biff