INDEX for non-adjacent cells? for Google Sheets

[Gru]

Hello

*** all data is fictional ***

I have data in a single row, consisting of multiple "groups" or "sets" of data. In my screenshots below, in Cell "Q3", I would like excel to search across "E3:K3" and"S3:AM3" for the highest date (MAX date). Once that date is found, find the associated value in the column with the header "Aggregate ACB"; that is, the cell that is 5 cells to the right of the "date" cell.

Is that possible? From what I understand, the INDEX function isn't working here since my array does not consist of adjacent cells. Perhaps a formula coud look at the entire row (E3:AM3) but exclude the "group" in which the cell is being calculated; that is, in the example below, look at E3:AM3, but exclude the range of L3:R3.

Would something like that work? Hopefully I'm making my explanation clear. Thank you for your help!

 

[offthelip]

welcome to the forum try this:

=INDEX(E1:AM1,1,MATCH((MAX(MAX(E3:K3),MAX(S3:AM3))),E3:AM3,0))

 

[Gru]

welcome to the forum try this:

=INDEX(E1:AM1,1,MATCH((MAX(MAX(E3:K3),MAX(S3:AM3))),E3:AM3,0))

That doesn't seem to be working:

 

[offthelip]

do you have a #ref error in AE as shown in your post because that could be the cause of the ref error. you could try just the match part and see if that come up with anything sensible:

MATCH((MAX(MAX(E3:K3),MAX(S3:AM3))),E3:AM3,0)

if that is still in error try:

(MAX(MAX(E3:K3),MAX(S3:AM3)))

I just realised I didn't put $ in the index line:
try this:

=INDEX(E$:AM$1,1,MATCH((MAX(MAX(E3:K3),MAX(S3:AM3))),E3:AM3,0))

 

[Fluff]

@Gru questions about Google Sheets should be posted in the General Discussion & Other Applications section of the board & you need to make it clear what system you are using (preferably in the title).
I have done that for you this time.

 

[Gru]

Sorry about that. Yes, this is Google Sheets. Apologies.

@offthelip I still can't get it to work. Perhaps I'm doing something wrong.

I put a link below to my example Excel spreadsheet. Maybe it's better if I remove all my other data to try and get this to work first. Let's start from scratch.
Basically, in this example, in cell "Q3", I need Excel to look at the range "E3:K3" and "Z3:AT3" for the MAX date. Then, once that MAX date is found, give me the associated "Aggregate ACB" numerical value from the cell that is 5 cells to the right of the MAX date cell.

In my example, the value that the formula should ideally return is $244.00 (cell AL3 - this is the "Aggregate ACB" from the MAX date of 2017-06-05 in cell AG3).

Example spreadsheet link (OneDrive)

 

[offthelip]

have you tried the simpler formulae e.g. does this find the right date:

=INDEX(E3:AM3,1,MATCH((MAX(MAX(E3:K3),MAX(S3:AM3))),E3:AM3,0))

I note you have changed the ranges since your first post, I haven't changed my equations
I have just realised that since you are putting this in Q3 it isn't going to work because the equation refers to itself. So forget this approach.
What I suggest is using a "helper" column off to the right of your columns where you can put the equation and then refer to it in Q3
Alternatively do it using VBA

 

[Gru]

have you tried the simpler formulae e.g. does this find the right date:

=INDEX(E3:AM3,1,MATCH((MAX(MAX(E3:K3),MAX(S3:AM3))),E3:AM3,0))

I note you have changed the ranges since your first post, I haven't changed my equations
I have just realised that since you are putting this in Q3 it isn't going to work because the equation refers to itself. So forget this approach.
What I suggest is using a "helper" column off to the right of your columns where you can put the equation and then refer to it in Q3
Alternatively do it using VBA

Thanks @offthelip . What would this "helper" column look like and where exactly do you recommend that I place it?

Also, I have zero experience at all with VBA so I'm not sure what that would entail.

Thanks again for all your assistance.

 

[offthelip]

I have just seen the update about this being about google sheets, I was answering assuming it was excel, VBA doesn't work for google sheets and I don't use google sheets so I have no idea whether what I proposed would work. Hopefully somebody else will respond