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

[SeanLCA]

30 Investors Rate Lender A Lender B Lender C Lender D Lender E 6.125 #N/A 105.801 #N/A #N/A #N/A 6.000 #N/A 105.192 0.000 105.782 105.382 5.875 0.000 104.883 0.000 105.558 105.158 5.750 105.092 104.674 104.498 105.299 105.435 5.625 104.603 104.150 104.107 104.883 104.946 5.500 104.177 103.728 103.711 104.495 104.52 5.375 103.819 103.405 103.601 104.145 104.162 5.250 103.387 103.113 103.427 103.725 103.73 5.125 102.906 102.610 102.975 103.256 103.249 5.000 102.198 101.946 102.353 102.563 102.541 4.875 101.726 101.466 101.586 102.079 102.069 4.750 101.135 100.881 100.832 101.476 101.478 4.625 100.533 #N/A 100.497 100.852 100.876 4.500 99.846 #N/A 99.874 100.165 #N/A 4.375 99.089 #N/A 98.998 99.402 #N/A 4.250 98.427 #N/A 98.304 #N/A #N/A 4.125 97.716 #N/A 97.648 #N/A #N/A <colgroup><col><col><col><col><col><col></colgroup><tbody> </tbody> <strike></strike>	<strike></strike>
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
Thanks

<tbody>
</tbody>

 

[shift-del]

Hi

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
1	Rate	Lender A	Lender B	Lender C	Lender D	Lender E
2	6,125	#N/A	105,801	#N/A	#N/A	#N/A
3	6,000	#N/A	105,192	0,000	105,782	105,382
4	5,875	0,000	104,883	0,000	105,558	105,158
5	5,750	105,092	104,674	104,498	105,299	105,435
6	5,625	104,603	104,150	104,107	104,883	104,946
7	5,500	104,177	103,728	103,711	104,495	104,520
8	5,375	103,819	103,405	103,601	104,145	104,162
9	5,250	103,387	103,113	103,427	103,725	103,730
10	5,125	102,906	102,610	102,975	103,256	103,249
11	5,000	102,198	101,946	102,353	102,563	102,541
12	4,875	101,726	101,466	101,586	102,079	102,069
13	4,750	101,135	100,881	100,832	101,476	101,478
14	4,625	100,533	#N/A	100,497	100,852	100,876
15	4,500	99,846	#N/A	99,874	100,165	#N/A
16	4,375	99,089	#N/A	98,998	99,402	#N/A
17	4,250	98,427	#N/A	98,304	#N/A	#N/A
18	4,125	97,716	#N/A	97,648	#N/A	#N/A

<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>
</tbody>

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

<tbody>
</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?

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

<tbody>
</tbody>

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