excelexcel-formulaspreadsheet

Excel Index+ match Returns duplicate value

The company I work for uses excel sheets to find the cheapest available fuel in their desired fueling location. I have managed to make the formulas of the sheet they had already in place work properly except for 1 very specific case where more than 1 location offers fuel at the same price.

Image Example

Basically in the example:

For columns H, J and L, the formula calculates the first, second and third cheapest fuel location.

Formulas:

=Small(C3:G3;Countif(C3:G3;0)+1)
=Small(C3:G3;Countif(C3:G3;0)+2)
=Small(C3:G3;Countif(C3:G3;0)+3)

For columns I, K and M, it finds the $$ matching the cell next to it with the first row's station Name.

In this case since Courcelle, lac Megantic and St-Gedeon all sell the fuel at the same price (1,4505) it returns 3X Courcelle as answer.

Formulas:

=index($C$1:$G$1;Match(H3;C3:G3;0))
=index($C$1:$G$1;Match(J3;C3:G3;0))
=index($C$1:$G$1;Match(L3;C3:G3;0))`

Is it possible, without reworking the entire sheet, to either:

  1. Return the cheapest station but if the name is already there in cell 1 or 2, skip to the next.

  2. Return all Station names that matches the cheapest price, then lookup the next highest price and follow as if

Here is what I wish it would return / expected result

Expected Results

Solution

  • You could try using the following formula, assuming there is no excel constraints as per the post tags:

    enter image description here

    =LET(
     _x, A3:E3, 
     _y, TAKE(SORT(UNIQUE(A3:E3,1),,,1),,3), 
     TOROW(VSTACK(XLOOKUP(_y,_x,$A$1:$E$1),_y),,1))

    You may need to change the cell reference or ranges as per your suit.

    Or as you expected output shown in the second screenshot:

    enter image description here

    =LET(
     _x, A3:E3,
     _y, $A$1:$E$1,
     _z, SORT(UNIQUE(_x,1),,,1),
     _List, BYCOL(_z,LAMBDA(a,TEXTJOIN(CHAR(10),1,FILTER(_y,a=_x)))),
     TOROW(VSTACK(_z,_List),,1))

    Use the following if there will be any blanks :

    =LET(
     _x, A3:E3,
     _y, $A$1:$E$1,
     _z, SORT(UNIQUE(_x,1),,,1),
     _List, BYCOL(_z,LAMBDA(a,TEXTJOIN(CHAR(10),1,FILTER(_y,(a=_x)*(_x<>0),NA())))),
     TOROW(IF(_List=0,NA(),VSTACK(_z,_List)),2,1))