#Value for Excel Small function which shows correct result in formula builder

1
votes

I am writing a formula to get the row number of a date within a table.

I have 2 excel worksheets, Sheet1 contains data as follows:

        A           B   
1     Date        Value
2     21/2/2018     3
3     22/2/2018     4
4     22/2/2018     1
5     23/2/2018     2

Sheet2 has the following (Cell C1 contains the date I am looking for):

         A            B          C        
1   <my formula>             22/2/2018   
2

my formula is =SMALL((IF(Sheet1!$A$1:$A$5=$C$1,ROW(Sheet1!A$1:A$5))),ROW(A$1))

in the formula builder it is showing 3 as expected as the first instance of 22/2/2018 is in the third cell down. However the cell (A1 on sheet2) is returning #value.

1
excelexcel-formulaarray-formulas
Aren't you looking for =SMALL(IF(Sheet1!$A$1:$A$5=$C$1, Sheet1!B$1:B$5), ROW(A$1)) ? – user4039065
Are you using ctrl+shift+enter and not just enter? – user4039065
@Jeeped No I am looking for the date so that I can complete the rest of the table with the date and value ultimately – IMO

1 Answers

0
votes

Try this array formula.

=SMALL(IF(Sheet1!A$1:A$5=H$2, ROW(Sheet1!$1:$5)), ROW(1:1))
'alternately as a standard formula
=AGGREGATE(15, 6, ROW(Sheet1!$1:$5)/(Sheet1!A$1:A$5=H$2), ROW(1:1))

Finish with ctrl+shift+enter, not just enter. G2 is a standard INDEX formula.

=INDEX(B:B, F2)

enter image description here