Last matching Value in multiple Columns

[Emre06]

Hello, i have 2 Columns containing guards's names. ( I tried to use "maxifs" function but it is working only for one column ) My Question is: I need formula to find "Last Date" of "John Fizz" in "Beach"?

Kitap1 (version 1).xlsb
	A	B	C	D
1	Date	Guard 1	Guard 2	Location
2	1.02.2023	Mike Rogers	Romeo Loi	Lobby
3	2.02.2023	John Fizz	Taric Ben	Beach
4	3.02.2023	Alan Dusk	Lora Ian	Hotel
5	4.02.2023	Clark Kent	John Fizz	Beach
Sayfa1

 

[BigBeachBananas]

Change MAX to COUNTA

=COUNTA(FILTER(A:A,((B:B= "John FIzz")+(C:C="John Fizz"))*ISNUMBER(SEARCH("Beach",D:D))))

 

[BigBeachBananas]

My mistake, the error will always return a count of 1. How about this?

=SUMPRODUCT(--NOT(ISERROR(FILTER(A:A,((B:B="John Fizz")+(C:C="John Fizz"))*ISNUMBER(SEARCH("Beach",D:D))))))

 

[Alex Blakenburg]

FYI - SumProduct will make in backward compatible but in Excel 2021 and greater (incl MS365) just Sum will work.

 

[Alex Blakenburg]

I don't understand what is different.
This ISNUMBER(SEARCH("Beach",D:D)), is the equivalent of looking for *Beach*
So what is it not doing ?

 

[Emre06]

Thank you very much Sir! Tried and worked If you don't mind, could you explain how it works ?

 

[Emre06]

Thank you very much.

(XMATCH("John FizzBeach",C:C&D:D,0,-1)

İs it possible to use cells for"JohnFizzBeach" like
(XMATCH(B3&D3,C:C&D:D,0,-1)

 

[Emre06]

Thank you it worked. I tried to use same formula for others but there is a problem. If I use same formula for "Mike Rogers" ( Hes only been as First Guard not second) formula gives error ?

 

[Emre06]

@BigBeachBananas Thank you very much !

@Emre06 - you also need to aware that the XMatch option supplied gets the "last matching" value in the table (that is what the "-1" at the end of the function does - tells it to look from the bottom up). This means your date column needs to be sorted in ascending order.

Here is another option:
Note:
* is AND logic ie both need to be true
+ is OR logic ie one or more need to be true

=MAX(
            FILTER( $A$2:$A$10,
                          (($B$2:$B$10=G2)*($D$2:$D$10=H2))
                          +(($C$2:$C$10=G2)*($D$2:$D$10=H2))
                          )
           )

Thank you very much for you kind help Sir. Sorry But Firstly cannot copy code, I think spaces are giving me problem. And G2 value and H2 value is empty I suppose ? I couldnt manage to use that

 

[Emre06]

=MAX(FILTER(A:A,(B:B="John Fizz")*(D:D="Beach")+(C:C="John Fizz")*(D:D="Beach")))

Replace "John Fizz" and "Beach" with your cell references.

Thank you very much for your help, I was kind of lost; now I got it.

1) Did you
a) click on the copy symbol in the top right corner of the grey area that contains the formula ?
b) copy the code directly into the formula bar ? (not into the cell)
2) G2 is where I had the name to be used by the formula and H2 where I had the location, you need to put in your own cell references for that.
Your example shows B3 & D3 but seems a bit unlikely since it is in the range to be searched.

BigBeachBananas has now supplied the flattened out version, I was just trying to make the formula logic more obvious by using spaces and Alt+Enter to structure the formula

Thank you very much, now thanks to you without errors I have found the last dates.