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.

CodePudding user response：

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)),"")

CodePudding user response：

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}),"")

CodePudding user response：

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.

CodePudding user response：

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