I have an Excel file were I have 3 columns. I will explain the steps:

Step 1: I will be giving an input age (eg: 25).

Step 2: Then we need to match the minimum and maximum column .

Step 3: Once matched the value it should return from age group (eg 25-35). Multiple age groups can also be returned.

I need help with this Excel formula.

I have tried index match but no success.

CodePudding user response：

There might be an easier way, but this is an option:

=INDEX($A$2:$A$8,MATCH(MINIFS($C$2:$C$8,$C$2:$C$8,">="&F33),$C$2:$C$8))

(where F33 is the cell with the age)

Explanation:

=MINIFS($C$2:$C$8,$C$2:$C$8,">="&F33)

This returns the minimum value which is greater than the given age in the table. I tried to use this function to directly return the range name, but it doesn't work; it only returns a number. So from the second range, it picks all the values which satisfy the condition, and then returns the corresponding (same row) value from the first range. E.g.: if age=52, from the second range it will pick 55 and 60 (just as filters), and then take the minimum value from the last two rows of the first range (55).

=MATCH(MINIFS(...),$C$2:$C$8)

Looks for the MINIFS result value (55) in the range and returns the row index; 6 in this case. First row is 1, and remember it's relative to the range, not the whole sheet.

=INDEX($A$2:$A$8,MATCH(...))

Just picks rows 6 from the given range.