Finding last match meeting multiple conditions

Discuss the spreadsheet application
Post Reply
sacstars
Posts: 1
Joined: Fri Jul 06, 2018 6:13 am

Finding last match meeting multiple conditions

Post by sacstars »

=SUMPRODUCT(MAX((table="c")*ROW(table)))

This works fine on an unsorted list
However i do not understand how this works

How can i use 2 conditions instead of 1 ?
Eg.- A:A="c" AND B:B="y"
NeoOffice 2.2.3 with MacOS 10.4
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] VLOOKUP/whatever: find last match in unsorted l

Post by Lupp »

Welcome!

A few remarks.
-1- You are the first user of NeoOffice posting here to my knowledge. I don't know anything specific about the behaviour of NO.
-2- Isn't NO a commercial variant? Aren't they offering support of their own?
-3- This is a very old thread . I didnt read all the posts since it was opened.
-4- Your formula does not use VLOOKUP(). Your post is exclusively about "How to find the last match"
-5- I might have been better to start a new thread.

(Obsolete since the question was movd to a new thread meanwhile.)
-6- AOO still doesn't accept full-column-ranges (nor full-row-ranges) in a shorthand notation like "A:A".
-7- The formula you posted doesn't find the last match in the sense of an index into the range table, but the highest RowNumber found for a matching content. This only is meaningless if the data actually start in the first row.
-8- The SUMPRODUCT() is only used to force array-evaluation upon the enclosed expression. You might also enter only that expression with Ctrl+Shift+Enter to explicitly make it "force-array". It would show then as {=MAX((table="c")*ROW(table))} when not in edit mode.
-9- In the formula you used, the first factor on the parameter position of MAX() is a condition returning False or True. Used in a arithmetic expression these results are evaluated as 0 and 1 respectively. You can combine conditions using arithmetic operators. (Cond1)*(Cond2) acts combining the conditions with AND, ((Cond1+Cond2)>=1) acts like OR. [((Cond1+Cond2)=1) makes an XOR.]
-10- Multiplying the arithmetic-logical result with the ROW() expression annulates it if the logical result was False, and accounts for it, in the True case. MAX() will thus return the highest RowNumber with a match. [You cannot use MIN() in the same way!]
-11- Regarding -8-, -9-, and -10- your problem is solved with:

Code: Select all

=SUMPRODUCT(MAX((A:A="c")*(B:B="y")*ROW(A:A)))
Last edited by Lupp on Fri Jul 06, 2018 2:27 pm, edited 1 time in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] VLOOKUP/whatever: find last match in unsorted l

Post by robleyd »

G'day Lupp

We have a few users of NeoOffice on Mac from time to time. This poster however appears to be using a version of NeoOffice about 10 years old - based on OOo 2.3 or thereabouts - with a Mac using an operating system released in 2005.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Sumproduct multiple conditions

Post by Lupp »

@robleyd: Thanks for your activity!
[BTW: I would have preferred a topic like "Finding last match meeting multiple conditions" since te usage of SUMPRODUCT() was peripheral ("force-array"). ]

:shock: Well, I was released in 1944 and basically my OS may be about 4E6 years old. I'm fine. :D
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply