Ask Your Question

# Solved index/max search [closed]

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.

edit retag reopen merge delete

## 2 Answers

Sort by » oldest newest most voted

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)

more

## Comments

lol that must be some radio code :))

( 2018-01-25 10:17:03 +0200 )edit

Not "some", but international :-)

( 2018-01-25 10:21:06 +0200 )edit

(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.

( 2018-01-26 11:09:26 +0200 )edit

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.

( 2018-01-26 13:26:36 +0200 )edit

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

( 2018-01-26 21:33:06 +0200 )edit

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

( 2018-01-27 08:05:13 +0200 )edit

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.

more

## Comments

Go to offline help F1 and search LARGE or online help

IF should be self explanatory

( 2018-01-25 13:13:25 +0200 )edit

## Stats

Asked: 2018-01-25 06:53:25 +0200

Seen: 152 times

Last updated: Jan 27 '18