Xlookup help?

Hello. I don't understand the complete power of "xlookup" yet; I've previously been using Vlookup.

(all data below is fictious)

In Cell "AL3", I want Excel to grab the most recent "Aggregate ACB". All of the data (attached) below occurs in row 3 (imagine a very long, single row).

In the scenario below, in cell "AL3", I want Excel to look across columns "E3:K3", "L3:R3", "S3:Y3", and "Z3:AF3" for the latest date, which is always found in the first column of each "group". Once that latest date is found, I want Excel to grab the "Aggregate ACB" from that "group" (the "6th" column in each "group").

Is that possible? Does my explanation make sense?

Thanks for your help!

Excel file attached. Thanks.

It appears that Google Sheets does not allow the use of the "LET" function. Is there an alternative function to this?

Alternative formula =INDEX(E3:AF3,MATCH(MAXIFS(E3:AF3,E$2:AF$2,"Date*"),E3:AF3,0)+5).

Originally Posted by josephteh

Alternative formula =INDEX(E3:AF3,MATCH(MAXIFS(E3:AF3,E$2:AF$2,"Date*"),E3:AF3,0)+5).

Thanks! Both those work perfectly.

I have a follow-up question, if you don't mind:


What if in Cell "AL3", I want Excel to grab the most recent "Aggregate ACB", but only from "group", "E3:K3", and "Y3:AF3"? In other words, the array is not a group of adjacent cells. I want Excel to only look at a certain set of cells.
Your solution above worked because it simply looked at a "straight" array of "E3:AF3"; but not the array does not consist of single, adjacent group of cells.

And sorry, I shouldn't keep using the word "Excel". This is in Google Sheets, which I thought was just Excel inside Google, but apparently not. This is because only Google Sheets has access to "=GOOGLEFINANCE( ...".

Thanks!

Sorry, TSM. Typo in my above post. It should have said "EF:AF3"; but [what if] the array does not consist of a a single, adjacent group of cells?"
Also, I should have re-phrased the opening few words of that sentence; I realize now that I may have come off sounding offensive - not my intent at all.

Apologies. Your original formula works great and is fantastic. Thank you for providing it.

All I intended to ask was: is there a way to slightly alter that formula that you provided, to be able to consider groups of cells that are non-adjacent, such as "E3:K3", and "Y3:AF3"?

Thanks again for your help. Much appreciated.

Ok. I was previously using the "IF" function but that only worked for two dates.

What if, in cell "Q3" I want Excel to look at the range of cells "E3:K3", and "S3:AT3" for the MAX date, and then once that date is found, give me the value under column "Aggregate ACB" for that "group"?

I tried using the "AND(" function inside of the previous formula but Excel didn't accept that. Something like: =INDEX(AND(E3:K3,S3:AT3),MATCH(MAXIFS(AND(E3:K3,S3:AT3),E$2:AF$2,"Date*"),E3:AF3,0)+5). But that didn't work.

Basically, trying to make the formula look at a multiple, non-adjacent cells.

Thanks.