hello,
I am looking for the excel function to find closet value from "Answer Column" from below table, where answer row should satisfy all given conditions from Input area.
DATA Table -
[TABLE="width: 676"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]
[TABLE="width: 676"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Sr.
No.[/TD]
[TD]Data
Set[/TD]
[TD]Answer
Column[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Alfa[/TD]
[TD]A[/TD]
[TD]860[/TD]
[TD]153[/TD]
[TD]360[/TD]
[TD]360[/TD]
[TD]540[/TD]
[TD]540[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alfa[/TD]
[TD]B[/TD]
[TD]1200[/TD]
[TD]214[/TD]
[TD]410[/TD]
[TD]410[/TD]
[TD]625[/TD]
[TD]625[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Alfa[/TD]
[TD]C[/TD]
[TD]1600[/TD]
[TD]320[/TD]
[TD]455[/TD]
[TD]455[/TD]
[TD]680[/TD]
[TD]680[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Beta[/TD]
[TD]D[/TD]
[TD]2000[/TD]
[TD]452[/TD]
[TD]510[/TD]
[TD]510[/TD]
[TD]760[/TD]
[TD]760[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Beta[/TD]
[TD]E[/TD]
[TD]2500[/TD]
[TD]601[/TD]
[TD]570[/TD]
[TD]570[/TD]
[TD]840[/TD]
[TD]840[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Beta[/TD]
[TD]F[/TD]
[TD]3000[/TD]
[TD]992[/TD]
[TD]660[/TD]
[TD]660[/TD]
[TD]840[/TD]
[TD]840[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Alfa[/TD]
[TD]G[/TD]
[TD]4500[/TD]
[TD]1860[/TD]
[TD]780[/TD]
[TD]780[/TD]
[TD]980[/TD]
[TD]980[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
INPUT Table -
[TABLE="width: 729"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Conditions[/TD]
[/TR]
[TR]
[TD]Input 1[/TD]
[TD]Alfa[/TD]
[TD]Select Data Set with applting filter[/TD]
[/TR]
[TR]
[TD]Input 2[/TD]
[TD]590[/TD]
[TD]Input 2 <= A[/TD]
[/TR]
[TR]
[TD]Input 3[/TD]
[TD]60[/TD]
[TD]Input 3 <= (B*80%)[/TD]
[/TR]
[TR]
[TD]Input 4[/TD]
[TD]20[/TD]
[TD]Any Number[/TD]
[/TR]
[TR]
[TD]Input 5 (Max)[/TD]
[TD]600[/TD]
[TD]1. findout Max out of Input 5& Input6.
2. Min Input Value+(2*Input 4) <= D[/TD]
[/TR]
[TR]
[TD]Input 6
(Min)[/TD]
[TD]300[/TD]
[TD]1. findout Max out of Input 5& Input6.
2. Max Input <= E[/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD]"B"[/TD]
[TD]Closest Value from "Answer Column" where in that row all above conditions satisfies.[/TD]
[/TR]
</tbody>[/TABLE]
Guyz i need help.
I am looking for the excel function to find closet value from "Answer Column" from below table, where answer row should satisfy all given conditions from Input area.
DATA Table -
[TABLE="width: 676"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]
[TABLE="width: 676"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Sr.
No.[/TD]
[TD]Data
Set[/TD]
[TD]Answer
Column[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Alfa[/TD]
[TD]A[/TD]
[TD]860[/TD]
[TD]153[/TD]
[TD]360[/TD]
[TD]360[/TD]
[TD]540[/TD]
[TD]540[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alfa[/TD]
[TD]B[/TD]
[TD]1200[/TD]
[TD]214[/TD]
[TD]410[/TD]
[TD]410[/TD]
[TD]625[/TD]
[TD]625[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Alfa[/TD]
[TD]C[/TD]
[TD]1600[/TD]
[TD]320[/TD]
[TD]455[/TD]
[TD]455[/TD]
[TD]680[/TD]
[TD]680[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Beta[/TD]
[TD]D[/TD]
[TD]2000[/TD]
[TD]452[/TD]
[TD]510[/TD]
[TD]510[/TD]
[TD]760[/TD]
[TD]760[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Beta[/TD]
[TD]E[/TD]
[TD]2500[/TD]
[TD]601[/TD]
[TD]570[/TD]
[TD]570[/TD]
[TD]840[/TD]
[TD]840[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Beta[/TD]
[TD]F[/TD]
[TD]3000[/TD]
[TD]992[/TD]
[TD]660[/TD]
[TD]660[/TD]
[TD]840[/TD]
[TD]840[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Alfa[/TD]
[TD]G[/TD]
[TD]4500[/TD]
[TD]1860[/TD]
[TD]780[/TD]
[TD]780[/TD]
[TD]980[/TD]
[TD]980[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
INPUT Table -
[TABLE="width: 729"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Conditions[/TD]
[/TR]
[TR]
[TD]Input 1[/TD]
[TD]Alfa[/TD]
[TD]Select Data Set with applting filter[/TD]
[/TR]
[TR]
[TD]Input 2[/TD]
[TD]590[/TD]
[TD]Input 2 <= A[/TD]
[/TR]
[TR]
[TD]Input 3[/TD]
[TD]60[/TD]
[TD]Input 3 <= (B*80%)[/TD]
[/TR]
[TR]
[TD]Input 4[/TD]
[TD]20[/TD]
[TD]Any Number[/TD]
[/TR]
[TR]
[TD]Input 5 (Max)[/TD]
[TD]600[/TD]
[TD]1. findout Max out of Input 5& Input6.
2. Min Input Value+(2*Input 4) <= D[/TD]
[/TR]
[TR]
[TD]Input 6
(Min)[/TD]
[TD]300[/TD]
[TD]1. findout Max out of Input 5& Input6.
2. Max Input <= E[/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD]"B"[/TD]
[TD]Closest Value from "Answer Column" where in that row all above conditions satisfies.[/TD]
[/TR]
</tbody>[/TABLE]
Guyz i need help.