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]

Try this.

=INDEX(A:A,MAX(XMATCH("John FizzBeach",B:B&D:D,0,-1),XMATCH("John FizzBeach",C:C&D:D,0,-1)))

 

[Emre06]

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

 

[BigBeachBananas]

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

This bit is looking for the row index from the bottom up for the string "John FizzBeach" when columns B and D join together. (Guard 1)

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

Same thing for this bit but looking in columns C and D (Guard 2)

At this point, you have a row index for Guard 1 and Guard 2, but you want the latest so we apply the MAX().

Then use INDEX() to return the date from Column A, with the row index.

 

[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)

 

[BigBeachBananas]

Yes, it's possible.

 

[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 ?

 

[Alex Blakenburg]

@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))
                          )
           )

 

[BigBeachBananas]

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 ?

This will work when that happens but @Alex Blakenburg has a shorter solution.

=LET(guard1,XMATCH(C2&D2,B:B&D:D,0,-1),guard2,XMATCH(C2&D2,C:C&D:D,0,-1),IFERROR(INDEX(A:A,MAX(IF(ISNUMBER(guard1),guard1,-1),IF(ISNUMBER(guard2),guard2,-1))),"Not found"))

 

[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