• 516K Members
• 6,663 Online
• 612K Conversations
SOLVED

New Contributor

Assistance needed with Index/Match or another alternative

Could someone please assist with the following:

1. Sheet1 contains a number of different scales (Scale1, Scale2, Scale3...) that are numeric or alphanumeric.
2. The main scale has values 0 to 100. All scales are as rows.
3. Sheet 2 contains a drop down to show all the available scales from sheet 1
4. If a user selects a scale from the drop down (e.g., Scale2) and enters a value, then the corresponding value from the main scale should be displayed.
5. A value on Scale2 can correspond to multiple values on the main scale.
6. The formula should display the highest corresponding value from the main scale.

Example:

Scale 2 Values are: A1, A1, A1, A1, A2, A2... Main Scale Values 100, 99, 98, 97, 96, 95. So A1 can equal 100, 99, 98 or 97. Only 100 (the highest value) should be displayed.

Thanks

4 Replies

Re: Assistance needed with Index/Match or another alternative

Hi
Sounds like you could use MAXIFS to bring back the max value from Main Scale that corresponds to the selected Scale

e.g. = MAXIFS( MainScaleColumn, ScaleColumn, SelectedScale)
Highlighted
Solution

Re: Assistance needed with Index/Match or another alternative

@Spicierboar , as variant, for such model

`=AGGREGATE(14,6,1/(INDEX(A3:G4,MATCH(B6,A3:A4,0),2):INDEX(A3:G4,MATCH(B6,A3:A4,0),7)=B7)*\$B\$2:\$G\$2,1)`

Re: Assistance needed with Index/Match or another alternative

@Sergei Baklan This works. Thank you.

Re: Assistance needed with Index/Match or another alternative

@Spicierboar , you are welcome

Related Conversations
Matching Excel cells by column
ddelise in Excel on
3 Replies