Returning data label when using a 'Small' formula

[TheGent]

I am using the below formula to return the smallest number in a range.

=SMALL(IF('YoY View - Dep & Cat'!$D$6:$D$108='Call Outs'!$C$4,'YoY View - Dep & Cat'!$U$6:$U$108),$B5)

I would like to return the label of the result, I'm having difficulty doing so.

For example the above formula would return '201.4' which is correct but I want a formula that will now return the label that accompanies this number i.e. Dave.

 

[jim may]

Try wrapping your existing formula inside the Offset() function.

=Offset("YourExistingFormula",X,Y) when X is # of Rows to offset and Y is the # of Columns to offset

 

[TheGent]

Try wrapping your existing formula inside the Offset() function.

=Offset("YourExistingFormula",X,Y) when X is # of Rows to offset and Y is the # of Columns to offset

No luck with the above - I should have mentioned the existing formula is an array.

 

[jim may]

After {Editing} you did of course commit with the Key-Board strokes - Control+Shift+Enter - Right?
If so then sorry -- I can't offer anything else. Jim

 

[TheGent]

After {Editing} you did of course commit with the Key-Board strokes - Control+Shift+Enter - Right?
If so then sorry -- I can't offer anything else. Jim

Indeed.

 

[jim may]

Is "Indeed" good news, or bad news for you?

 

[TheGent]

Indeed I did use Ctrl+Shift+Enter and therefore this is bad news.

Struggling...

 

[njimack]

Which column is the label in, and what result would you expect if more than one label had the same value?

 

[TheGent]

My label is in 'YoY View - Dep & Cat'!$F$6:$F$108

The way this is built I wouldn't expect duplicate values to be an issue as I want to return the exact label for the exact value returned.

I currently have a workaround but this does not take into account if there are any duplicates...

=INDEX('YoY View - Dep & Cat'!$F$112:$F$214,MATCH('Call Outs'!$E8,'YoY View - Dep & Cat'!$U$112:$U$214,0))

 

[Marcelo Branco]

Say the labels are in column A, that is, 'YoY View - Dep & Cat'!$A$6:$A$108 (adjust to suit)

Try
=INDEX('YoY View - Dep & Cat'!$A$6:$A$108,MATCH(your_formula_here,IF('YoY View - Dep & Cat'!$D$6:$D$108='Call Outs'!$C$4,'YoY View - Dep & Cat'!$U$6:$U$108),0))
Ctrl+Shift+Enter

M.