I need a function to get desired column by matching other column values

Question

I have two tables.

Table 1

Table 2

Now I want a function to compare Test column and when they are equal (for example row 2 of table matches row 3 and 4 of table 2) I need to compare the price column to check the max (in this example row 3 600 is greater than row 4 550) and then return my chosen column. So this should return "pixel as Value1" and "4 as rank".

I tried VLOOKUP, IF but was not able to merge them to get the desired result. Any help is appreciated.

Answer 1

Here's another option if you're not running Excel 365

=INDEX($H$2:$H$5,MATCH(AGGREGATE(14,4,($G$2:$G$5=A2)*$J$2:$J$5,1),$J$2:$J$5,0))

and

=INDEX($I$2:$I$5,MATCH(B2,$H$2:$H$5,0))

The formulas above are in cells B2 and C2, as per the image below

Answer 2

In case you don't have Excel-365, you could do like this.

My formula in cell B3 is:

=INDEX($B$14:$B$17;MATCH(A3&SUMPRODUCT(MAX(--($A$14:$A$17=A3)*$D$14:$D$17));$A$14:$A$17&$D$14:$D$17;0))

Formula in cell C3 is almost the same, just change first argument on INDEX to retrieve Rank column:

=INDEX($C$14:$C$17;MATCH(A3&SUMPRODUCT(MAX(--($A$14:$A$17=A3)*$D$14:$D$17));$A$14:$A$17&$D$14:$D$17;0))

Notice both formulas are array formulas so they must be entered pressing CTRL+SHIFT+ENTER or it won't work!

Also, if the test is not found, it will raise an error. For example, now with values a and d won't work because those values are not present in Table2.

Answer 3

Quite similar to @Harun's answer, but I used:

Formula in B2:

=IFERROR(INDEX(SORT(FILTER(E$2:H$5,E$2:E$5=A2),4,-1),{2,3}),"")

Answer 4

If you have Excel-365 they could use FILTER() and SORT() function with INDEX(). Try-

 =IFERROR(INDEX(SORT(FILTER($E$9:$G$12,$D$9:$D$12=$A2),3,-1),1,COLUMN(A$1)),"")