Ask Your Question
0

Solved index/max search

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

vk7krj gravatar image

updated 2018-01-26 00:59:36 +0200

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
2

answered 2018-01-25 09:41:35 +0200

JohnSUN gravatar image

updated 2018-01-27 08:12:54 +0200

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)

edit flag offensive delete link more

Comments

lol that must be some radio code :))

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

Not "some", but international :-)

JohnSUN gravatar imageJohnSUN ( 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.

Lupp gravatar imageLupp ( 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.

JohnSUN gravatar imageJohnSUN ( 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

Lupp gravatar imageLupp ( 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.

JohnSUN gravatar imageJohnSUN ( 2018-01-27 08:05:13 +0200 )edit
0

answered 2018-01-25 12:49:28 +0200

vk7krj gravatar image

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.

edit flag offensive delete link more

Comments

Go to offline help F1 and search LARGE or online help

IF should be self explanatory

robleyd gravatar imagerobleyd ( 2018-01-25 13:13:25 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 93 times

Last updated: Jan 27 '18