Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find the second value if it is a tie
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
find the second value if it is a tie
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
find the second value if it is a tie
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
find the second value if it is a tie
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace | Excel Worksheet Functions | |||
Code needed to find records from bottom up | Excel Discussion (Misc queries) | |||
find a cells from a range of cell | Excel Worksheet Functions | |||
How do I find a column entry closest to a particular value | Excel Worksheet Functions | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |