I have a list of ham radio contacts in a speadsheet, I am trying to find the last occurence of a callsign- ie, the last time the station was worked.
I am using index/match to do the job, but it consistently returns the fourth entry in the column regardless of the callsign searched for.
The formula is as follows-
=INDEX(dates,SUMPRODUCT(MAX((callsigns=$M$3)*ROW(callsigns))))
I have used named ranges to make it a bit easier on my brain-
Dates is A1:A150 and is the date the station was worked.
Callsigns is E1:E150, the callsign of the station.
M3 is the cell where I input the callsign I am searching for.
As an example, here are the first four rows of the sheet-
A B C D E
2017-12-31 09:41:45 2017-12-31 09:42:45 VK4FNQ
2018-01-02 10:59:45 2018-01-02 11:02:15 VK7ZBX
2018-01-03 21:34:00 2018-01-03 21:35:00 VK2IR
2018-01-06 03:12:00 2018-01-06 03:13:00 VK3ASC
The date and time I need is in A & B, the callsign I am searching for is in E.
I have done endless searching on the web without a solution, so any help would be greatly appreciated. I must also say, I am fairly new to spreadsheets so keeping the answers as simple as possible would help greatly.
Thanks, Ken, vk7krj.