find the second value if it is a tie

[cloud]

suppose 23 degrees fall on 1st of jan and 31st of jan.

how do i use match,index so tat the answer will show 31st jan instead of
23rd jan and i am not allowed to use sorting.

[flummi]

Try this code in a command button. Search value in D1, data in A1:A20

Private Sub CommandButton1_Click()
Set sh = Worksheets(ActiveSheet.Name)
cv = sh.Cells(1, 4).Value
For i = 1 To 20
dv = sh.Cells(i, 2)
sv = sh.Cells(i, 1)
If sv = cv Then
If dv > lastdate Then
lastdate = dv
End If
End If
Next i
If lastdate <> "" Then
sh.Cells(1, 3).Value = lastdate
End If

End Sub


Hans

[Ron Coderre]

If you want the last date that a specific temp occurred, try something like
this:

With dates in cells A1:A100 and temps in B1:B100

C1: (the temp you want to find)
D1: =INDEX(A1:A100,SUMPRODUCT(MAX((B1:B100=C1)*ROW(B1:B100))),1)

(D1 is formatted as a date)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"cloud" wrote:

> suppose 23 degrees fall on 1st of jan and 31st of jan.
>
> how do i use match,index so tat the answer will show 31st jan instead of
> 23rd jan and i am not allowed to use sorting.

[pinmaster]

If I understand you correctly you want to return the latest date if you have
a tie, if so then try this array formula:

assuming B1:B10 is your degrees and A1:A10 your dates
=MAX(IF(B1:B10=23,A1:A10))
enter using Ctrl+Shift+Enter

HTH
Jean-Guy

"cloud" wrote:

> suppose 23 degrees fall on 1st of jan and 31st of jan.
>
> how do i use match,index so tat the answer will show 31st jan instead of
> 23rd jan and i am not allowed to use sorting.