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