• 412K Members
• 5,194 Online
• 468K 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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies