Finding Best and Send Best Value and ignore #N/A

[SeanLCA]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 505"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]30[/TD]
[TD="colspan: 5"]Investors[/TD]
[/TR]
[TR]
[TD]Rate[/TD]
[TD]Lender A[/TD]
[TD]Lender B[/TD]
[TD]Lender C[/TD]
[TD]Lender D[/TD]
[TD]Lender E[/TD]
[/TR]
[TR]
[TD="align: right"]6.125[/TD]
[TD]#N/A[/TD]
[TD]105.801[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]6.000[/TD]
[TD]#N/A[/TD]
[TD]105.192[/TD]
[TD]0.000[/TD]
[TD]105.782[/TD]
[TD]105.382[/TD]
[/TR]
[TR]
[TD="align: right"]5.875[/TD]
[TD]0.000[/TD]
[TD]104.883[/TD]
[TD]0.000[/TD]
[TD]105.558[/TD]
[TD]105.158[/TD]
[/TR]
[TR]
[TD="align: right"]5.750[/TD]
[TD]105.092[/TD]
[TD]104.674[/TD]
[TD]104.498[/TD]
[TD]105.299[/TD]
[TD]105.435[/TD]
[/TR]
[TR]
[TD="align: right"]5.625[/TD]
[TD]104.603[/TD]
[TD]104.150[/TD]
[TD]104.107[/TD]
[TD]104.883[/TD]
[TD]104.946[/TD]
[/TR]
[TR]
[TD="align: right"]5.500[/TD]
[TD]104.177[/TD]
[TD]103.728[/TD]
[TD]103.711[/TD]
[TD]104.495[/TD]
[TD]104.52[/TD]
[/TR]
[TR]
[TD="align: right"]5.375[/TD]
[TD]103.819[/TD]
[TD]103.405[/TD]
[TD]103.601[/TD]
[TD]104.145[/TD]
[TD]104.162[/TD]
[/TR]
[TR]
[TD="align: right"]5.250[/TD]
[TD]103.387[/TD]
[TD]103.113[/TD]
[TD]103.427[/TD]
[TD]103.725[/TD]
[TD]103.73[/TD]
[/TR]
[TR]
[TD="align: right"]5.125[/TD]
[TD]102.906[/TD]
[TD]102.610[/TD]
[TD]102.975[/TD]
[TD]103.256[/TD]
[TD]103.249[/TD]
[/TR]
[TR]
[TD="align: right"]5.000[/TD]
[TD]102.198[/TD]
[TD]101.946[/TD]
[TD]102.353[/TD]
[TD]102.563[/TD]
[TD]102.541[/TD]
[/TR]
[TR]
[TD="align: right"]4.875[/TD]
[TD]101.726[/TD]
[TD]101.466[/TD]
[TD]101.586[/TD]
[TD]102.079[/TD]
[TD]102.069[/TD]
[/TR]
[TR]
[TD="align: right"]4.750[/TD]
[TD]101.135[/TD]
[TD]100.881[/TD]
[TD]100.832[/TD]
[TD]101.476[/TD]
[TD]101.478[/TD]
[/TR]
[TR]
[TD="align: right"]4.625[/TD]
[TD]100.533[/TD]
[TD]#N/A[/TD]
[TD]100.497[/TD]
[TD]100.852[/TD]
[TD]100.876[/TD]
[/TR]
[TR]
[TD="align: right"]4.500[/TD]
[TD]99.846[/TD]
[TD]#N/A[/TD]
[TD]99.874[/TD]
[TD]100.165[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]4.375[/TD]
[TD]99.089[/TD]
[TD]#N/A[/TD]
[TD]98.998[/TD]
[TD]99.402[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]4.250[/TD]
[TD]98.427[/TD]
[TD]#N/A[/TD]
[TD]98.304[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]4.125[/TD]
[TD]97.716[/TD]
[TD]#N/A[/TD]
[TD]97.648[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I have a list of 5 lenders offering a rate of return on different interest rates. how do I highlight the best and send best price at each rate. For example, at 6.00 rate, lender D is the best price at 105.782 then it be lender E at 105.382 and formula is able to ignore the N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shift-del]

Hi

	A	B	C	D	E	F

<colgroup><col style="width: 28ptpx"><col width="34,5pt"><col width="48,75pt"><col width="46,5pt"><col width="46,5pt"><col width="47,25pt"><col width="45,75pt"></colgroup><tbody>
[TD="colspan: 7"]Arbeitsblatt mit dem Namen 'Tabelle1'[/TD]

[TD="bgcolor: #cacaca"]1[/TD]
[TD="align: left"]Rate[/TD]
[TD="align: left"]Lender A[/TD]
[TD="align: left"]Lender B[/TD]
[TD="align: left"]Lender C[/TD]
[TD="align: left"]Lender D[/TD]
[TD="align: left"]Lender E[/TD]

[TD="bgcolor: #cacaca"]2[/TD]
[TD="align: right"]6,125[/TD]
[TD="align: left"]#N/A[/TD]
[TD="bgcolor: #70AD47, align: right"]105,801[/TD]
[TD="align: left"]#N/A[/TD]
[TD="align: left"]#N/A[/TD]
[TD="align: left"]#N/A[/TD]

[TD="bgcolor: #cacaca"]3[/TD]
[TD="align: right"]6,000[/TD]
[TD="align: left"]#N/A[/TD]
[TD="align: right"]105,192[/TD]
[TD="align: right"]0,000[/TD]
[TD="bgcolor: #70AD47, align: right"]105,782[/TD]
[TD="bgcolor: #FFC000, align: right"]105,382[/TD]

[TD="bgcolor: #cacaca"]4[/TD]
[TD="align: right"]5,875[/TD]
[TD="align: right"]0,000[/TD]
[TD="align: right"]104,883[/TD]
[TD="align: right"]0,000[/TD]
[TD="bgcolor: #70AD47, align: right"]105,558[/TD]
[TD="bgcolor: #FFC000, align: right"]105,158[/TD]

[TD="bgcolor: #cacaca"]5[/TD]
[TD="align: right"]5,750[/TD]
[TD="align: right"]105,092[/TD]
[TD="align: right"]104,674[/TD]
[TD="align: right"]104,498[/TD]
[TD="bgcolor: #FFC000, align: right"]105,299[/TD]
[TD="bgcolor: #70AD47, align: right"]105,435[/TD]

[TD="bgcolor: #cacaca"]6[/TD]
[TD="align: right"]5,625[/TD]
[TD="align: right"]104,603[/TD]
[TD="align: right"]104,150[/TD]
[TD="align: right"]104,107[/TD]
[TD="bgcolor: #FFC000, align: right"]104,883[/TD]
[TD="bgcolor: #70AD47, align: right"]104,946[/TD]

[TD="bgcolor: #cacaca"]7[/TD]
[TD="align: right"]5,500[/TD]
[TD="align: right"]104,177[/TD]
[TD="align: right"]103,728[/TD]
[TD="align: right"]103,711[/TD]
[TD="bgcolor: #FFC000, align: right"]104,495[/TD]
[TD="bgcolor: #70AD47, align: right"]104,520[/TD]

[TD="bgcolor: #cacaca"]8[/TD]
[TD="align: right"]5,375[/TD]
[TD="align: right"]103,819[/TD]
[TD="align: right"]103,405[/TD]
[TD="align: right"]103,601[/TD]
[TD="bgcolor: #FFC000, align: right"]104,145[/TD]
[TD="bgcolor: #70AD47, align: right"]104,162[/TD]

[TD="bgcolor: #cacaca"]9[/TD]
[TD="align: right"]5,250[/TD]
[TD="align: right"]103,387[/TD]
[TD="align: right"]103,113[/TD]
[TD="align: right"]103,427[/TD]
[TD="bgcolor: #FFC000, align: right"]103,725[/TD]
[TD="bgcolor: #70AD47, align: right"]103,730[/TD]

[TD="bgcolor: #cacaca"]10[/TD]
[TD="align: right"]5,125[/TD]
[TD="align: right"]102,906[/TD]
[TD="align: right"]102,610[/TD]
[TD="align: right"]102,975[/TD]
[TD="bgcolor: #70AD47, align: right"]103,256[/TD]
[TD="bgcolor: #FFC000, align: right"]103,249[/TD]

[TD="bgcolor: #cacaca"]11[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]102,198[/TD]
[TD="align: right"]101,946[/TD]
[TD="align: right"]102,353[/TD]
[TD="bgcolor: #70AD47, align: right"]102,563[/TD]
[TD="bgcolor: #FFC000, align: right"]102,541[/TD]

[TD="bgcolor: #cacaca"]12[/TD]
[TD="align: right"]4,875[/TD]
[TD="align: right"]101,726[/TD]
[TD="align: right"]101,466[/TD]
[TD="align: right"]101,586[/TD]
[TD="bgcolor: #70AD47, align: right"]102,079[/TD]
[TD="bgcolor: #FFC000, align: right"]102,069[/TD]

[TD="bgcolor: #cacaca"]13[/TD]
[TD="align: right"]4,750[/TD]
[TD="align: right"]101,135[/TD]
[TD="align: right"]100,881[/TD]
[TD="align: right"]100,832[/TD]
[TD="bgcolor: #FFC000, align: right"]101,476[/TD]
[TD="bgcolor: #70AD47, align: right"]101,478[/TD]

[TD="bgcolor: #cacaca"]14[/TD]
[TD="align: right"]4,625[/TD]
[TD="align: right"]100,533[/TD]
[TD="align: left"]#N/A[/TD]
[TD="align: right"]100,497[/TD]
[TD="bgcolor: #FFC000, align: right"]100,852[/TD]
[TD="bgcolor: #70AD47, align: right"]100,876[/TD]

[TD="bgcolor: #cacaca"]15[/TD]
[TD="align: right"]4,500[/TD]
[TD="align: right"]99,846[/TD]
[TD="align: left"]#N/A[/TD]
[TD="bgcolor: #FFC000, align: right"]99,874[/TD]
[TD="bgcolor: #70AD47, align: right"]100,165[/TD]
[TD="align: left"]#N/A[/TD]

[TD="bgcolor: #cacaca"]16[/TD]
[TD="align: right"]4,375[/TD]
[TD="bgcolor: #FFC000, align: right"]99,089[/TD]
[TD="align: left"]#N/A[/TD]
[TD="align: right"]98,998[/TD]
[TD="bgcolor: #70AD47, align: right"]99,402[/TD]
[TD="align: left"]#N/A[/TD]

[TD="bgcolor: #cacaca"]17[/TD]
[TD="align: right"]4,250[/TD]
[TD="bgcolor: #70AD47, align: right"]98,427[/TD]
[TD="align: left"]#N/A[/TD]
[TD="bgcolor: #FFC000, align: right"]98,304[/TD]
[TD="align: left"]#N/A[/TD]
[TD="align: left"]#N/A[/TD]

[TD="bgcolor: #cacaca"]18[/TD]
[TD="align: right"]4,125[/TD]
[TD="bgcolor: #70AD47, align: right"]97,716[/TD]
[TD="align: left"]#N/A[/TD]
[TD="bgcolor: #FFC000, align: right"]97,648[/TD]
[TD="align: left"]#N/A[/TD]
[TD="align: left"]#N/A[/TD]

</tbody>

Zelle	bedingte Formatierung...	Format
B2	1: LARGE($B2:$F2,2)=B2
B2	2: LARGE($B2:$F2,1)=B2

<tbody>
[TD="bgcolor: #FFC000"]abc[/TD]

[TD="bgcolor: #70AD47"]abc[/TD]

</tbody>

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

 

[SeanLCA]

That didn’t work for me

 

[Fluff]

How about
=B2=AGGREGATE(14,6,$B2:$F2,2)
and
=B2=AGGREGATE(14,6,$B2:$F2,1)

 

[trimmjens]

Hi,

Maybe this can help?

[TABLE="class: outer_border, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"] G [/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Rate[/TD]
[TD]Lender A[/TD]
[TD]Lender B[/TD]
[TD]Lender C[/TD]
[TD]Lender D[/TD]
[TD]Lender E[/TD]
[TD][/TD]
[TD]Best Lender[/TD]
[TD="align: center"]Price[/TD]
[TD]Worst Lender[/TD]
[TD="align: center"]Price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6,125[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]105,801[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Lender B[/TD]
[TD="align: right"]105,801[/TD]
[TD]Lender B[/TD]
[TD="align: right"]105,801[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6,000[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]105,192[/TD]
[TD="align: right"]0,000[/TD]
[TD="align: right"]105,782[/TD]
[TD="align: right"]105,382[/TD]
[TD][/TD]
[TD]Lender D[/TD]
[TD="align: right"]105,782[/TD]
[TD]Lender C[/TD]
[TD="align: right"]0,000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5,875[/TD]
[TD="align: right"]0,000[/TD]
[TD="align: right"]104,883[/TD]
[TD="align: right"]0,000[/TD]
[TD="align: right"]105,558[/TD]
[TD="align: right"]105,158[/TD]
[TD][/TD]
[TD]Lender D[/TD]
[TD="align: right"]105,558[/TD]
[TD]Lender A[/TD]
[TD="align: right"]0,000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5,750[/TD]
[TD="align: right"]105,092[/TD]
[TD="align: right"]104,674[/TD]
[TD="align: right"]104,498[/TD]
[TD="align: right"]105,299[/TD]
[TD="align: right"]105,435[/TD]
[TD][/TD]
[TD]Lender E[/TD]
[TD="align: right"]105,435[/TD]
[TD]Lender C[/TD]
[TD="align: right"]104,498[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5,625[/TD]
[TD="align: right"]104,603[/TD]
[TD="align: right"]104,150[/TD]
[TD="align: right"]104,107[/TD]
[TD="align: right"]104,883[/TD]
[TD="align: right"]104,946[/TD]
[TD][/TD]
[TD]Lender E[/TD]
[TD="align: right"]104,946[/TD]
[TD]Lender C[/TD]
[TD="align: right"]104,107[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5,500[/TD]
[TD="align: right"]104,177[/TD]
[TD="align: right"]103,728[/TD]
[TD="align: right"]103,711[/TD]
[TD="align: right"]104,495[/TD]
[TD="align: right"]104,520[/TD]
[TD][/TD]
[TD]Lender E[/TD]
[TD="align: right"]104,520[/TD]
[TD]Lender C[/TD]
[TD="align: right"]103,711[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]5,375[/TD]
[TD="align: right"]103,819[/TD]
[TD="align: right"]103,405[/TD]
[TD="align: right"]103,601[/TD]
[TD="align: right"]104,145[/TD]
[TD="align: right"]104,162[/TD]
[TD][/TD]
[TD]Lender E[/TD]
[TD="align: right"]104,162[/TD]
[TD]Lender B[/TD]
[TD="align: right"]103,405[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]5,250[/TD]
[TD="align: right"]103,387[/TD]
[TD="align: right"]103,113[/TD]
[TD="align: right"]103,427[/TD]
[TD="align: right"]103,725[/TD]
[TD="align: right"]103,730[/TD]
[TD][/TD]
[TD]Lender E[/TD]
[TD="align: right"]103,730[/TD]
[TD]Lender B[/TD]
[TD="align: right"]103,113[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]5,125[/TD]
[TD="align: right"]102,906[/TD]
[TD="align: right"]102,610[/TD]
[TD="align: right"]102,975[/TD]
[TD="align: right"]103,256[/TD]
[TD="align: right"]103,249[/TD]
[TD][/TD]
[TD]Lender D[/TD]
[TD="align: right"]103,256[/TD]
[TD]Lender B[/TD]
[TD="align: right"]102,610[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]5,000[/TD]
[TD="align: right"]102,198[/TD]
[TD="align: right"]101,946[/TD]
[TD="align: right"]102,353[/TD]
[TD="align: right"]102,563[/TD]
[TD="align: right"]102,541[/TD]
[TD][/TD]
[TD]Lender D[/TD]
[TD="align: right"]102,563[/TD]
[TD]Lender B[/TD]
[TD="align: right"]101,946[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]4,875[/TD]
[TD="align: right"]101,726[/TD]
[TD="align: right"]101,466[/TD]
[TD="align: right"]101,586[/TD]
[TD="align: right"]102,079[/TD]
[TD="align: right"]102,069[/TD]
[TD][/TD]
[TD]Lender D[/TD]
[TD="align: right"]102,079[/TD]
[TD]Lender B[/TD]
[TD="align: right"]101,466[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]4,750[/TD]
[TD="align: right"]101,135[/TD]
[TD="align: right"]100,881[/TD]
[TD="align: right"]100,832[/TD]
[TD="align: right"]101,476[/TD]
[TD="align: right"]101,478[/TD]
[TD][/TD]
[TD]Lender E[/TD]
[TD="align: right"]101,478[/TD]
[TD]Lender C[/TD]
[TD="align: right"]100,832[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]4,625[/TD]
[TD="align: right"]100,533[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]100,497[/TD]
[TD="align: right"]100,852[/TD]
[TD="align: right"]100,876[/TD]
[TD][/TD]
[TD]Lender E[/TD]
[TD="align: right"]100,876[/TD]
[TD]Lender C[/TD]
[TD="align: right"]100,497[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4,500[/TD]
[TD="align: right"]99,846[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]99,874[/TD]
[TD="align: right"]100,165[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Lender D[/TD]
[TD="align: right"]100,165[/TD]
[TD]Lender A[/TD]
[TD="align: right"]99,846[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4,375[/TD]
[TD="align: right"]99,089[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]98,998[/TD]
[TD="align: right"]99,402[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Lender D[/TD]
[TD="align: right"]99,402[/TD]
[TD]Lender C[/TD]
[TD="align: right"]98,998[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]4,250[/TD]
[TD="align: right"]98,427[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]98,304[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Lender A[/TD]
[TD="align: right"]98,427[/TD]
[TD]Lender C[/TD]
[TD="align: right"]98,304[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]4,125[/TD]
[TD="align: right"]97,716[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]97,648[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Lender A[/TD]
[TD="align: right"]97,716[/TD]
[TD]Lender C[/TD]
[TD="align: right"]97,648[/TD]
[/TR]
</tbody>[/TABLE]

Formula in H2: =OFFSET(INDIRECT(ADDRESS(ROW();MATCH(AGGREGATE(4;6;$B2:$F2);$B2:$F2+1;2));-ROW()+1;0)
Formula in H3: =OFFSET(INDIRECT(ADDRESS(ROW();MATCH(AGGREGATE(4;6;$B3:$F3);$B3:$F3+1;2));-ROW()+1;0)
and so on...

Formula in I2: =AGGREGATE(4;6;B2:F2)
Formula in I3: =AGGREGATE(4;6;B3:F3)
and so on...

Formula in J2: =OFFSET(INDIRECT(ADDRESS(ROW();MATCH(AGGREGATE(5;6;$B2:$F2);$B2:$F2+1;2));-ROW()+1;0)
Formula in J3: =OFFSET(INDIRECT(ADDRESS(ROW();MATCH(AGGREGATE(5;6;$B3:$F3);$B3:$F3+1;2));-ROW()+1;0)
and so on...

Formula in K2: =AGGREGATE(5;6;$B2:$F2)
Formula in K3: =AGGREGATE(5;6;$B3:$F3)
and so on...

I guess you need to change the semicolons to commas to get the formulas to work on your computer.

This may not me the easiest approach, but it seems to work if it was something like this you were looking for.


-Thomas