finding the least cost number from a group of reference numbers

Question

Hello, I am looking to&nbsp; have excel return the results of the least cost option from reference number 1, then 2 then 3 etc..&nbsp; We are trying to get optimal results using multiple vendors.&nbsp; &nbsp;&nbsp;&nbsp;Please advise how this can be done.&nbsp;&nbsp;Ref number&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Results1&nbsp; &nbsp;FXFE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $133.21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FXFE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $133.211&nbsp; &nbsp;CNWY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$418.981&nbsp; &nbsp;DAYF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$418.982&nbsp; &nbsp;FXFE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $210.71&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FXFE&nbsp; &nbsp; &nbsp; &nbsp; $210.712&nbsp; &nbsp;CNWY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$411.852&nbsp; &nbsp;DAYF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$234.883&nbsp; &nbsp;FXFE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $334.263&nbsp; &nbsp;CNWY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$135.713&nbsp; &nbsp;DAYF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$114.46&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DAYF&nbsp; &nbsp; &nbsp; $114.46

oliverscheurich · Answer

dtiede&nbsp;Thank you for adding the expected result. I hope the formula in the attached sample file works for you.

hansvogelaar · Answer

dtiede&nbsp;
Assuming that your data are in columns A, B and C, starting in row 2:
Enter the following formula in another cell in row 2, for example in E2:
&nbsp;
=IF($C2=MINIFS($C$2:$C$1000, $A$2:$A$1000, $A2), $B2:$C2, "")
&nbsp;
Fill down.

harun24hr · Answer

dtiede&nbsp;If you are on Microsoft-365 then could try single cell dynamic array formula.=HSTACK(
MAP(B2:B10,C2:C10,D2:D10,LAMBDA(x,y,z,IF(MINIFS(D2:D10,B2:B10,x)=z,y,""))),
MAP(B2:B10,C2:C10,D2:D10,LAMBDA(x,y,z,IF(MINIFS(D2:D10,B2:B10,x)=z,z,"")))
)&nbsp;&nbsp;

lorenzo · Answer

For the record, with Power Pivot:

Forum Discussion

finding the least cost number from a group of reference numbers

4 Replies

Resources