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]

@BigBeachBananas Thank you very much !


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

=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.

 

[Alex Blakenburg]

@BigBeachBananas Thank you very much !


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

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

 

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

 

[Emre06]

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

 

[Alex Blakenburg]

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

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.

 

[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

 

[Alex Blakenburg]

You are welcome. Glad the 2 of us were able to help.

 

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

 

[Alex Blakenburg]

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

 

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