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

 

[Emre06]

By the way is there a way to conditional format #N/A error ?

 

[Emre06]

I was going to throw that in but opted to keep it to the essentials in the first pass.
Just wrap the whole thing in an IFERROR,
So before the Max add
IFERROR(
then at the end add
,"")
Or you could but something like Not Found between the quotes.

Thank you very much for your understanding, now very tidy page I will be working with

 

[Emre06]

I have one more question about same formula: I am trying to use * asterisk like "*beach*" for this table. How can I make this work ( "Last Date" of "John Fizz" in "Beach"? " )

Kitap1.xlsx
	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,Lobby
6
7		Beach
8	John Fizz	0
Sayfa1

Cell Formulas
Range	Formula
B8	B8	=MAX(FILTER(A:A,(B:B="John Fizz")*(D:D="*Beach*")+(C:C="John Fizz")*(D:D="*Beach*")))

 

[Emre06]

Try this. I have also rearranged it a little so you don't need to evaluate the location twice.

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

Thank you very much Sir, you are amazing ! Do you know how can I find John Fizz has been in beach ? (2 times in this table)

I tried to change MAX with Countif but it didn't worked, Sorry.

 

[Emre06]

Change MAX to COUNTA

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

Thank you, but having a problem now. John fizz is 2 times in beach and counting correctly but, it is also counting 1 for others who hasn't been in beach.

 

[Emre06]

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

Thank you very much, customization took very long but it is working now.

 

[Emre06]

Try this. I have also rearranged it a little so you don't need to evaluate the location twice.

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

Hello again, now I am having a new problem. John Fizz can be in multiple places now on the same day (Beach, Hotel), but I just wanted to find the beach version. If I use I wanted to use Wildcard but the filter giving error, how can I solve this ?