User Defined Functions

A

ashishbarot

New Member
Joined
Apr 21, 2009
Messages
34
Pls pls pls help me for this prob......

I want to create the user defined function in excel in which i will assign the value to a char and after that i should b able to use it in formula.

Eg: for "FL" i will assign 1
for "LF" i will assign 2
for "VV1" i will assign 3
for "VV2" i will assign 4

and so on.......then i want to use this to compare both figures like
i will put the formula like if(VV1 < VV2,true,false) result should b true coz VV1 is having value of 3 and VV2 is having 4.

So basically how we create the custom lists i want to give the priority to my own list and want to use it in formula.

I hope some one from all the masters will help me out in this topic

Waiting for your reply.
 
Sort by date Sort by votes
<title>Excel Jeanie HTML</title>I tried it gave me error.............

I created new sheet List of Values

<title>Excel Jeanie HTML</title>List of Values

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>FL</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>VVS1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>VVS2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>VS1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>VS2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>SI1</td></tr></tbody></table>




<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 83px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>Find Max of :</td> <td>VVS2</td> <td>VVS1</td> <td>SI1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>#VALUE!</td> <td> </td> <td> </td> <td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>A2</td> <td>=INDEX('List of Values'!$A$1:$A$4,MAX(MATCH(B1:D1,'List of Values'!$A$1:$A$4,0)))</td></tr></tbody></table></td></tr></tbody></table>
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
As i told i created functions and then tried calling like u said....but still no reults

it gave me


<title>Excel Jeanie HTML</title>List of Values

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>IFL</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>VVS1</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>VVS2</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>VS1</td> <td> </td> <td>#NAME?</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>VS2</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>SI1</td> <td> </td> <td>#NAME?</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>C4</td> <td>=MIN(VVS1(A2),VVS2(A3),IFL(A1))</td></tr> <tr> <td>C6</td> <td>=MIN(VVS1(),VVS2(),IFL())</td></tr></tbody></table></td></tr></tbody></table>
 
Upvote 0
ashishbarot said:
I tried it gave me error.............

I created new sheet List of Values

List of Values

<TABLE style="BACKGROUND-COLOR: rgb(255,255,255); PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: rgb(202,202,202); FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: rgb(202,202,202); FONT-SIZE: 8pt">1</TD><TD>FL</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: rgb(202,202,202); FONT-SIZE: 8pt">2</TD><TD>VVS1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: rgb(202,202,202); FONT-SIZE: 8pt">3</TD><TD>VVS2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: rgb(202,202,202); FONT-SIZE: 8pt">4</TD><TD>VS1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: rgb(202,202,202); FONT-SIZE: 8pt">5</TD><TD>VS2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: rgb(202,202,202); FONT-SIZE: 8pt">6</TD><TD>SI1</TD></TR></TBODY></TABLE>




<TABLE style="BACKGROUND-COLOR: rgb(255,255,255); PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 83px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: rgb(202,202,202); FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: rgb(202,202,202); FONT-SIZE: 8pt">1</TD><TD>Find Max of :</TD><TD>VVS2</TD><TD>VVS1</TD><TD>SI1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: rgb(202,202,202); FONT-SIZE: 8pt">2</TD><TD>#VALUE!</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: rgb(0,255,0); BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: rgb(255,252,249); BORDER-TOP-COLOR: rgb(0,255,0); FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: rgb(0,0,0); BORDER-RIGHT-COLOR: rgb(0,255,0); FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: rgb(0,255,0)"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: rgb(202,202,202); FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A2</TD><TD>=INDEX('List of Values'!$A$1:$A$4,MAX(MATCH(B1:D1,'List of Values'!$A$1:$A$4,0)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Click to expand...


Please read me EDIT properly - the formula has to be entered with CTRL+SHIFT+ENTER, not just enter to work. Apart from that your fomula should be adjusted to reflect the complete range in the list of values sheet, so change all instances of 'List of Values'!$A$1:$A$4 to 'List of Values'!$A$1:$A$6.

 
Upvote 0
Sir it worked......thnx a Lot Lot Lot Lot

Now if i want to know that the value wat its returning is from which column...then

Basically i will have 3-4 entries like

P1 P2 P3 LB
IF VVS1 SI1 SI3

Your array will give the result which is IF...that's perfect but if want to know that instead of IF it should display 'P1' .....then
 
Upvote 0
I dont understand your question. Can you please post sample data and expected result using Excel Jeanie as you did in post #21?
 
Upvote 0
<title>Excel Jeanie HTML</title>Sheet3

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="color: rgb(0, 128, 0); font-size: 9pt; font-weight: bold;">Pur</td> <td style="color: rgb(0, 0, 255); font-size: 9pt; font-weight: bold;">Pur1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt; font-weight: bold;">Pur2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt; font-weight: bold;">final2</td> <td style="font-size: 9pt; font-weight: bold;">Array</td> <td style="font-size: 9pt; font-weight: bold;">Expected Result</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;"> </td> <td style="color: rgb(0, 0, 255); font-size: 9pt;"> </td> <td style="color: rgb(0, 0, 255); font-size: 9pt;"> </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;"> </td> <td style="font-size: 9pt;"> </td> <td style="font-size: 9pt;"> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VVS2 </td> <td style="font-size: 9pt;">VVS2</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">Pur1</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VVS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">IF </td> <td style="font-size: 9pt;">IF</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VVS1 </td> <td style="font-size: 9pt;">VVS1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">20</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">21</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">22</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">23</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">24</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">25</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VVS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VVS1 </td> <td style="font-size: 9pt;">VVS1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">26</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">27</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI2 </td> <td style="font-size: 9pt;">SI2</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">28</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">29</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI3</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI3</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI2 </td> <td style="font-size: 9pt;">SI2</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">30</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">31</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI2 </td> <td style="font-size: 9pt;">SI2</td> <td style="font-size: 9pt;">final2</td> <td> </td></tr></tbody></table>
 
Upvote 0
sorry, your request does not make sense to me. Your list contains values that are not listed in your List of Values in Post #21 (especially IF). Furthermore it is not clear to me which criteria you use to get your expected result. If I look at your rows 6 & 7 for instance I see identical values in the 4 cells Pur - final2, except for Pur2 and Final2 being switched. Yet in Row 6 you expect final 2, in row 7 Pur1.
It is also not clear how you determine which of the duplicate, triplicate, or qadruplicate values in Columns B - E in one row should be choosen for the expected result.
Please explain your logic and the purpose of the whole exercise.
 
Last edited:
Upvote 0
Sure i'll xplain wat i want.....

first of all just go through the sample data

Now there r 2 types of data col data and pur data

My intention is all the the 2 columns except final1 and final2 are from my side. And final1 and final2 r from outside I want to know that whether they r giving me the same grades as mine or they r giving better or lower than me. Only for this i m doing this excercise.

For col, col1, col2 i have created a formula through which it shows me the better or weaker grades, so i consider all the 3 columns from my side as One and try to compare it with final1.

Now observe If Condition Column.......in that if final1 is having the data which better or equal to mine then i have displayed final1 else it should show "Col". Row4 i have H, G(7), H(8) and G.....here incol1 i have G which is equal to final and there is no grade which is better than G. So i accepted the data. And in row 16 i have J, I(8), J(1) and J.....here in col1 i have better grade than J i.e. I(8). So I displayed Col

Same logic applies to pur,pur1,pur2 and final2.....but in this case i have to first use array, then after getting the result i have to compare it with final2, if result is equal to final2 then it will display final2 else Pur.

I know its a bit complicating....but there is no other option

Sheet3

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 72px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt; font-weight: bold;">Col</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt; font-weight: bold;">Col1</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt; font-weight: bold;">Col2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt; font-weight: bold;">final1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt; font-weight: bold;">IF Condition</td> <td style="color: rgb(128, 0, 0); font-size: 9pt; font-weight: bold;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt; font-weight: bold;">Pur</td> <td style="color: rgb(0, 0, 255); font-size: 9pt; font-weight: bold;">Pur1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt; font-weight: bold;">Pur2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt; font-weight: bold;">final2</td> <td style="font-size: 9pt; font-weight: bold;">Array</td> <td style="font-size: 9pt; font-weight: bold;">Expected Result</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>
</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">
</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">
</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">
</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">
</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="font-size: 9pt;">
</td> <td style="font-size: 9pt;">
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">H</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">G(7)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(8)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">G </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VVS2 </td> <td style="font-size: 9pt;">VVS2</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">H</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(6)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(5)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">H </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">F</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">F(9)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">G(2)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">F </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">D</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">E(6)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">D(8)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">D </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">Pur</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">H</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(8)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(8)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">H </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">E</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">F(5)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">E(6)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">E </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">G</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">G(2)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">G(5)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">F </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">H</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(7)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(8)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">H </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VVS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">IF </td> <td style="font-size: 9pt;">IF</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">K</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">J(8)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">J(7)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">J </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">G</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">G(5)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(2)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">G </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">H</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(6)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">I(3)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">H </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">G</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">G(6)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">G(6)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">G </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">J</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">I(8)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">J(1)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">J </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Col </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">I</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(6)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">I(7)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">I </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Col </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VVS1 </td> <td style="font-size: 9pt;">VVS1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">H</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(8)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">I(6)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">H </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">H</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(7)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(2)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">G </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">20</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">H</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(6)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">I(2)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">H </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">21</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">K</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">K(5)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">K(3)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">L </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Col </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">22</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">I</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(8)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">J(2)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">J </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Col </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS2 </td> <td style="font-size: 9pt;">VS2</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">23</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">F</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">F(3)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">F(9)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">F </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">24</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">H</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(4)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">I(2)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">H </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">25</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">I</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(4)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">I(8)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">I </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Col </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VVS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VVS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VVS1 </td> <td style="font-size: 9pt;">VVS1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">26</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">F</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">E(7)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">E(8)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">F </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Col </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">27</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">F</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">F(6)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">F(8)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">F </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI2 </td> <td style="font-size: 9pt;">SI2</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">28</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">I</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">I(4)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">J(1)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">I </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI1 </td> <td style="font-size: 9pt;">SI1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">29</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">H</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">H(5)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">I(2)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">H </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI3</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI3</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI2 </td> <td style="font-size: 9pt;">SI2</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">30</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">F</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">G(7)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">G(7)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">F </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">VS1</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">VS1 </td> <td style="font-size: 9pt;">VS1</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">31</td> <td style="text-align: center; color: rgb(0, 128, 0); font-size: 9pt;">F</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">D(5)</td> <td style="text-align: center; color: rgb(0, 0, 255); font-size: 9pt;">D(8)</td> <td style="text-align: center; color: rgb(128, 0, 0); font-size: 9pt;">D </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">Final1 </td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(0, 0, 255); font-size: 9pt;">SI2</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">SI2 </td> <td style="font-size: 9pt;">SI2</td> <td style="font-size: 9pt;">Final2</td> <td>
</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>E4</td> <td>=IF(AND(D4<=A4,D4<=B4,D4<=C4),"Final1","Col")</td></tr> <tr> <td>K4</td> <td>{=INDEX('List of Values'!$A$1:$A$8,MIN(MATCH(Sheet3!G4:J4,'List of Values'!$A$1:$A$8,0)))}</td></tr> <tr> <td>L4</td> <td>=IF(K4=J4,"Final2","Pur")</td></tr></tbody></table></td></tr></tbody></table>
 
Upvote 0
You can simply combine the formulas in K and L. Simply replace "K4" in the Formula in L4 with the index formula. and put it in K4 with CTRL+SHFT+ENTER. The resulting formula would be:

{=IF(INDEX('List of Values'!$A$1:$A$8,MIN(MATCH(Sheet3!G4:J4,'List of Values'!$A$1:$A$8,0)))=J4,"Final2","Pur")}.

If you want to replace "PUR" with the header of the actual column where the result of the index-formula appears first in it gets a bit more complicated:

Excel Workbook
ABCDEFGHIJKL
12ColCol1Col2final1IF ConditionPurPur1Pur2final2ArrayExpected Result
13
14HG(7)H(8)GFinal1VS1VVS2VS1VVS2VVS2Final2
15HG(7)H(8)GFinal1VS1VVS2VS1VVS2final2
Formulas
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
EDIT: replace ranges and MAX according to your needs.
 
Last edited:
Upvote 0
disregard the last part of my previous post. The formula is wrong. The following should work:
Excel Workbook
A
1FL
2L
3VVS2
4VS1
List of Values
Excel Workbook
ABCDEFGHIJKL
12ColCol1Col2final1IF ConditionPurPur1Pur2final2ArrayExpected Result
13
14HG(7)H(8)GFinal1VS1VVS2VS1VVS2VVS2Final2
15HG(7)H(8)GFinal1VS1VVS2VS1VVS2final2
Formulas
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Cell referencing issue
Replies
1
Views
110
RoryA
RoryA
C
  • Question Question
Send Email from Excel - Launch User's Default Email Client
Replies
3
Views
364
Micron
M
P
  • Question Question
how to save a macro workbook but as xlsx file
Replies
4
Views
518
RoryA
RoryA
Jeffrey Mahoney
  • Question Question
Dynamic Range of Rows
Replies
6
Views
192
Jeffrey Mahoney
Jeffrey Mahoney
L
  • Question Question
Hover Function in Excel Similar to PowerBI
Replies
0
Views
545
Lali2023
L

Forum statistics

Threads
1,223,922
Messages
6,175,406
Members
452,640
Latest member
steveridge

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top