Solved index/max search

[vk7krj]

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.

[JohnSUN]

Try this

=INDEX(dates, SUMPRODUCT(LARGE(IF(callsigns=$M$3, ROW(callsigns), ""), 1)), 1)

(or

=INDEX(dates; SUMPRODUCT(LARGE(IF(UPPER(callsigns)=UPPER($M$3); ROW(callsigns); ""); 1)); 1)

if you have this option enabled)

[librebel]

lol that must be some radio code :))

[JohnSUN]

Not “some”, but international

[Lupp]

(Apologies for my insistence.)
The semicolon (;) is the internationally accepted “function separator” (parameter list delimiter) in Calc. The comma is only an alternative for the display and for entering/editing used as default in many locales which nonetheless also accept the semicolon.
See also this thread.

[JohnSUN]

Wolfgang, do you mean that I should not have changed the original answer? I saw the note “I am fairly new to spreadsheets” and didn’t complicate solution for him: he use comma, I type comma too.

[Lupp]

I shouldn’t act as your guardian, of course. Apologies!
And I shouldn’t be obsessive concerning what I feel to be localization going astray- but I am.
(The OQ could paste the formula with semicolons without an error. His UI would simply change the separator for display. Someone pasting in a different locale with commas would fail.)
I hope I can forbear further insistence.
Regards

[JohnSUN]

No, no, you are absolutely right - I will supplement my answer with a standard spelling, no problem.

[vk7krj]

JohnSUN, many thanks, after playing with your formula for a while, I eventually found it was case-sensitive. All the data is in upper case and I was entering the query in lower case, assuming it was case-insensitive. When I accidentally put the callsign in in upper case, everthing worked as it should- it turns out I have had the correct formula for best part of a week!

Your formula worked straight away also, but I so far haven’t been able to find any web pages to help me understand the (LARGE(IF construct.

Thanks again for you help.

Ken, vk7krj.

[robleyd]

Go to offline help F1 and search LARGE or online help

IF should be self explanatory