Share via

Formula to Identify highest value in a row and display the value from a nearby cell

Anonymous
2022-12-25T07:49:19+00:00

Hi brains trust,

I have rows of data which contain demographics and then repeating date, weight, bmi.

e.g.

Name date weight bmi date weight bmi date weight bmi date weight bmi date weight bmi etc etc etc

In one cell I need to identify the max date from the row and display the corresponding BMI from 2 columns over. Finding the max date is easy enough with =(MAX(I12,L12,O12,R12,U12 etc, but I cannot for the life of me work out how to then display the BMI value from 2 cells over. E.g. if L12 contains the max date then I need my target cell to display the value of L14.

I have been trying various combinations of HLOOKUP, VLOOKUP, INDEX, CELL, MATCH, MAX without success.

If you can help I will owe you a beer (or a slurpee if you are under age!).

Thx

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. riny 20,870 Reputation points Volunteer Moderator
    2022-12-25T08:33:45+00:00

    Start by making the MAX formula a bit easier. Assuming that you are working with somewhat current dates, the underlying numerical value of the dates are probably somewhere near 44900. Format a date as General and you'll see what I mean. Since it's unlikely that either the weights or the BMI's are going to exceed any of the date values, you could, for instance use:

    =MAX(weight_data), let's say in L1, where weight_data is a named range referring to I12:W12

    To find the corresponding BMI, enter this:

    =INDEX(weight_data,1,MATCH(L1,weight_data,0)+2)

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-12-26T01:00:08+00:00

    Hi there

    Unfortunately, your scenario is not fully clear to us.

    Please try the formula =OFFSET($I$12,2,MATCH(MAX(I12,L12,O12,R12,U12),$I$12:$U$12,0)-1,1,1)

    I hope this helps you and gives a solution to your problem

    Do let me know if you need more help

    Regards

    Jeovany

    0 comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2022-12-26T00:03:44+00:00

    Hi,

    Share some data (in a form that can be pasted in an MS Excel file) and show the expected result.

    0 comments