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

 

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

 

[BigBeachBananas]

Yes, it's possible.

 

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

 

[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

 

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

 

[Alex Blakenburg]

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

 

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