INDEX and MATCH for the highest value in the list

Hi,

Attached is a file were in sheet1 I want to do some kind of look up (VLOOKUP, LOOKUP, INDEX and MATCH) of the values in Sheet2 (column B) so when the match is found between column A1 Sheet1 and Calumn A in Sheet2 that it returns the highest value ( which is 90).
I know I can use MAXIFS function to get the highest value. I can add that in column C Sheet2 and then do simple VLOOKUP from Sheet1 but can it be done all in one formula from Sheet1?

Second question how do I do it if I want it to return next highest value which in this example is (ABC12345/1 = 20, ABC12345/2 = 15, ABC12345/3 =25).

Regards
Cuni

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. YOu need to specify the nature of the lookup you want to do.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Post deleted following Ali's comment

Title now fine - thanks.

Highest:

=LARGE(IF(Sheet2!$A$2:$A$18=Sheet1!A2,Sheet2!$B$2:$B$18),1)

Next highest:

=LARGE(IF(Sheet2!$A$2:$A$18=Sheet1!A2,Sheet2!$B$2:$B$18),2)

and so on. All array entered (CTRL+SHIFT+ENTER).

You're welcome.

Hi,

Sorry I have to reopen the thread again. Aggregate function is new to me. It seems very useful and versatile...
I'm trying to wrap my head around array: Sheet2!$B$2:$B$18/(Sheet1!A2=Sheet2!$A$2:$A$18)
Area Sheet2!$B$2:$B$18 is divided by (Sheet1!A2=Sheet2!$A$2:$A$18) or? How can Excel still consider it to be array if so?

Hi

When you perform Sheet1!A2=Sheet2!$A$2:$A$18 you get an array of values true or false. In a arithmetic operation true is converted in 1 and false in 0. So, in your example, Sheet2!$B$2:$B$18/(Sheet1!A2=Sheet2!$A$2:$A$18) is the same as
{10;9;9;9;20;88;...;90} /("ABC12345/1"={"ABC12345/1";"ABC12345/1";"ABC12345/1";"ABC12345/1";"ABC12345/1";"ABC12345/1";"ABC12345/2";...;"ABC12345/3"})
{10;9;9;9;20;88;...;90} / {1;1;1;1;1;1;0;...;0}
which gives you the array ={10;9;9;9;20;88;#DIV/0!;...;#DIV/0!}.
The error is forced to be ignored by aggregate function (with 14 as larger and 6 as ignore errors option) and so, only the values associated to the component Sheet1!A2 are taken to perform the operation.

Sorry for my bad English but I hope this helps you to understand the question.

Yes, you do - that is precisely how to master this type of formula.

Please don't forget to mark this as solved.