'If" "and" option in VB function

[JayakrishnanJ]

Hello friends,

The following function i tried to use it in excel cell, but its too long, so i want to do it in VB, please help me in doing it in VB, I am bit new to this .

IF(C4="Aluminum Electrolytic Capacitor","CAPAE",
IF(AND(C4="Chip Array",E4="NP"),"CAPC",
IF(AND(C4="SIP",E4="NP"),"SIP",
IF(AND(C4="Chip",E4="NP"),"CAPC",
IF(AND(C4="Chip",E4="P"),"CAPCP",
IF(AND(C4="Axial Lead",E4="NP"),"CAPAD",
IF(AND(C4="Axial Lead",E4="P"),"CAPADP",
IF(AND(C4="Radial Lead",E4="NP"),CAPRD,
IF(AND(C4="Radial Lead",E4="P"),"CAPRDP",
IF(AND(C4="Molded Body",E4="NP"),"CAPM","CAPMP"))))))

Thanks,
Jayakrishnan J.

 

[Andrew Poulsom]

Welcome to MrExcel.

Create a table like this:

<TABLE style="WIDTH: 244pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=325 border=0 x:str><COLGROUP><COL style="WIDTH: 148pt; mso-width-source: userset; mso-width-alt: 7204" width=197><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 148pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=197 height=17>Name</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>NP</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>P</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Aluminum Electrolytic Capacitor</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CAPAE</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Chip Array</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CAPC</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SIP</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">SIP</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Chip</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CAPC</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Axial Lead</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CAPAD</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CAPADP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Radial Lead</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CAPRD</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CAPRDP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Molded Body</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CAPM</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CAPMP</TD></TR></TBODY></TABLE>

and name it Table using Insert|Name|Define.

Then try the formula:

=INDEX(Table,MATCH(C4,INDEX(Table,0,1),FALSE),MATCH(E4,INDEX(Table,1,0),FALSE))

Note that E4 must contain NP or P for each entry in C4.

 

[AlphaFrog]

I would probably use a table as Andrew suggests above but here's a formula you could use as well...

=IF(C4="Aluminum Electrolytic Capacitor","CAPAE",
LOOKUP(C4&E4,
{"Chip ArrayNP","SIPNP","ChipNP","ChipP","Axial LeadNP","Axial LeadP","Radial LeadNP","Radial LeadP","Molded BodyNP","Molded BodyP"},
{"CAPC","SIP","CAPC","CAPCP","CAPAD","CAPADP","CAPRD","CAPRDP","CAPM","CAPMP"}))

 

[JayakrishnanJ]

Thanks Andrew Poulsom quick solution,

It works fine, can you please tell me how this formula work

 

[Andrew Poulsom]

The formula looks down the first column of the table for what's in C4, across the first row of the table for what's in E4, and returns the value at the intersection. These 2 parts of the formula:

INDEX(Table,0,1)
INDEX(Table,1,0)

return the first column and row respectively.

 

[JayakrishnanJ]

AlphaFrog,

Thank you for solution, it woks perfect.

but i cant use it because the look up info that i am going to use will be much more that this, I am going to use Andrews solution.

thanks Again
Jayakrishnan J

 

[JayakrishnanJ]

Thank you,

Now I understood.

 

[rpmitchell]

Andrew, I am using the Index and Match formula that you provided in this thread, and it works perfectly, but I would like to use this in VBA. Can this be done?

 

[Andrew Poulsom]

Why would you want to use VBA when Excel's built-in functions will be much quicker?

 

[rpmitchell]

Well, I do a new download each month, and have to re-insert the formula into the new spreadsheet. I thought perhaps it would be faster to have a macro that simply inserts the formula automatically. But perhaps not.