In Excel, for the below table, I need a formula that I can drag down in Column C. It would look at Column A, find a duplicate, then bring the lower value of the duplicate from Column B associated with Column A. For example Apple has 2 values 104 and 105, hence it places 104 in Column C.
| A | B | C |
|---|---|---|
| Apple | 105 | FORMULA |
| Apple | 104 | 104 |
| Mango | 111 | |
| Pear | 115 | |
| Pear | 114 | 114 |
| Banana | 201 |
If you wanted to do this with the topmost value from Column B, you could just use a VLOOKUP function, like so:
=VLOOKUP(A2, A:B, 2, FALSE)
So, how can we invert this? Well, if you have excel-365 or excel-2021, then you can use the XLOOKUP function instead:
=XLOOKUP(A2, A:A, B:B, FALSE, NA(), 0, -1)
(That final -1 is the bit that tells it “Start searching from the bottom”)
If you don't have access to the XLOOKUP function, then you probably need to either Invert the Array with Offset, or do something fancy with the AGGREGATE function to grab the Maximum Row-value where that matches the current input.
Using COUNTIF and MINIFS. This will put the minimum value of the value in Column A on the last version of that value and only if it is duplicated:
=IF(AND(COUNTIF($A$1:A1,A1)=COUNTIF(A:A,A1),COUNTIF(A:A,A1)>1),MINIFS(B:B,A:A,A1),"")
If instead you want to put the value next to the minimum value for that criteria one can use:
=IF(AND(MINIFS(B:B,A:A,A1)=B1,COUNTIF(A:A,A1)>1),B1,"")
instead.
If one does not have MINIFS(), Replace the MINIFS(B:B,A:A,A1) with AGGREGATE(15,7,B:B/(A:A=A1),1)