# Can a single cell be concatenated?

#### kgoshia

##### Board Regular
Below is my single cell formula that is only 1/4 of the way completed, however excel only allows one more argument before it hits its limit of 64 functions.

=IFERROR(IF(AND(E5<=10,E11=1,E14=1),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=10,E11=1,E14=2),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=10,E11=1,E14=3),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=10,E11=2,E14=1),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=10,E11=2,E14=2),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=10,E11=2,E14=3),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=10,E11=3,E14=1),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=10,E11=3,E14=2),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=10,E11=3,E14=3),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=12.5,E11=1,E14=1),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=12.5,E11=1,E14=2),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=12.5,E11=1,E14=3),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=12.5,E11=2,E14=1),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=12.5,E11=2,E14=2),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=12.5,E11=2,E14=3),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=12.5,E11=3,E14=1),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=12.5,E11=3,E14=2),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=12.5,E11=3,E14=3),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=16,E11=1,E14=1),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=16,E11=1,E14=2),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=16,E11=1,E14=3),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=16,E11=2,E14=1),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=16,E11=2,E14=2),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=16,E11=2,E14=3),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=16,E11=3,E14=1),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=16,E11=3,E14=2),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=16,E11=3,E14=3),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=20,E11=1,E14=1),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=20,E11=1,E14=2),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=20,E11=1,E14=3),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=20,E11=2,E14=1),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=20,E11=2,E14=2),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=20,E11=2,E14=3),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=20,E11=3,E14=1),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=20,E11=3,E14=2),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=20,E11=3,E14=3),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=25,E11=1,E14=1),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=25,E11=1,E14=2),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=25,E11=1,E14=3),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=25,E11=2,E14=1),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=25,E11=2,E14=2),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=25,E11=2,E14=3),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=25,E11=3,E14=1),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=25,E11=3,E14=2),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=25,E11=3,E14=3),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=32,E11=1,E14=1),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=32,E11=1,E14=2),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=32,E11=1,E14=3),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=32,E11=2,E14=1),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=32,E11=2,E14=2),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=32,E11=2,E14=3),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=32,E11=3,E14=1),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=32,E11=3,E14=2),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=32,E11=3,E14=3),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=40,E11=1,E14=1),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=40,E11=1,E14=2),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=40,E11=1,E14=3),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=40,E11=2,E14=1),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=40,E11=2,E14=2),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=40,E11=2,E14=3),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=40,E11=3,E14=1),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=40,E11=3,E14=2),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=40,E11=3,E14=3),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,9,0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))),)

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### WarfritLive

##### Board Regular
Can you post a list of inputs /outputs? There's definitely a better way to do this.

#### kgoshia

##### Board Regular
This would be my VLOOKUP table I have below. The user has 3 items that to fill in to obtain the correct answer. First the user has to fill in the (V) for working votlage, then a (1,2 or 3) for a degree of protection, then the final argument would be a (I,II or III) for the type of material used to prove along with the other 2 condition to achieve proper protection from being electrically shocked

 RMS WORKING VOLTAGE up to and including V Pollution degree 1 (a) 2 3 Material group I, II, IIIa, IIIb I II IIIa, IIIb I II IIIa, IIIb (see Note) 10 0,08 0,4 0,4 0,4 1,0 1,0 1,0 12.5 0,09 0,42 0,42 0,42 1,05 1,05 1,05 16 0,1 0,45 0,45 0,45 1,1 1,1 1,1 20 0,11 0,48 0,48 0,48 1,2 1,2 1,2 25 0,125 0,5 0,5 0,5 1,25 1,25 1,25 32 0,14 0,53 0,53 0,53 1,3 1,3 1,3 40 0,16 0,56 0,8 1,1 1,4 1,6 1,8 50 0,18 0,6 0,85 1,2 1,5 1,7 1,9 63 0,2 0,63 0,9 1,25 1,6 1,8 2,0 80 0,22 0,67 0,9 1,3 1,7 1,9 2,1 100 0,25 0,71 1,0 1,4 1,8 2,0 2,1 125 0,28 0,75 1,05 1,5 1,9 2,1 2,4 160 0,32 0,8 1,1 1,6 2,0 2,2 2,5 200 0,42 1,0 1,4 2,0 2,5 2,8 3,2 250 0,56 1,25 1,8 2,5 3,2 3,6 4,0 320 0,75 1,6 2,2 3,2 4,0 4,5 5,0 400 1,0 2,0 2,8 4,0 5,0 5,6 6,3 500 1,3 2,5 3,6 5,0 6,3 7,1 8,0 630 1,8 3,2 4,5 6,3 8,0 9,0 10 800 2,4 4,0 5,6 8,0 10 11 12,5 1000 3,2 5,0 7,1 10 12,5 14 16 1250 4,2 6,3 9,0 12,5 16 18 20 1600 5,6 8,0 11 16 20 22 25 2000 7,5 10 14 20 25 28 32 2500 10 12,5 18 25 32 36 40 3200 12,5 16 22 32 40 45 50 4000 16 20 28 40 50 56 63 5000 20 25 36 50 63 71 80 6300 25 32 45 63 80 90 100 8000 32 40 56 80 100 110 125 10000 40 50 71 100 125 140 160

<colgroup><col><col><col span="2"><col><col span="3"></colgroup><tbody>
</tbody>

#### WarfritLive

##### Board Regular
I figured it out by staring at your formula. Use this:

=IFERROR(VLOOKUP(MATCH(E5,{10;12.5;16;20;25;32;40}),Mincreep!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),FALSE),"There's an error, yo")

Edit: You can add to the MATCH() array as needed without changing anything else.
ie. ...25;32;40;41;42;43;53;66;67;89;90}

Last edited:

#### SpillerBD

##### Well-known Member
I don't understand where the desired result is in your table.
I don't see how criteria 2 & 3 can independently vary a result based on your table.

#### SpillerBD

##### Well-known Member
I figured it out by staring at your formula. Use this:

=IFERROR(VLOOKUP(MATCH(E5,{10;12.5;16;20;25;32;40}),Mincreep!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),FALSE),"There's an error, yo")

Edit: You can add to the MATCH() array as needed without changing anything else.
ie. ...25;32;40;41;42;43;53;66;67;89;90}

I would make it a table or named range so to require the individual listing of the voltages. That array already exist, why manually duplicate it?

#### WarfritLive

##### Board Regular
SpillerBD, you are correct.
As I said, I created the formula based on studying his, before I had seen his follow up post.
Better formula is:

=IFERROR(VLOOKUP(MATCH(E5,'Minimum Creepage 2N'!A2:A50),'Minimum Creepage 2N'!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),F ALSE),"There's an error, yo")

#### kgoshia

##### Board Regular
Below are 2 pictures of the spreadsheet, the first picture shows the 3 question the user has to answer with the red circles around the cell and the cell will the green circle is the equation that I am stuck on.

[/URL][/IMG]

#### kgoshia

##### Board Regular
below in the VLOOKUP table

[/URL][/IMG]

#### WarfritLive

##### Board Regular
The array location is different than my last formula. Try this :

=IFERROR(VLOOKUP(MATCH(E5,'Minimum Creepage 2N'!A6:A26),'Minimum Creepage 2N'!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),F ALSE),"There's an error, yo")

Replies
6
Views
98
Replies
5
Views
204
Replies
4
Views
126
Replies
8
Views
257
Replies
5
Views
87

1,190,676
Messages
5,982,206
Members
439,767
Latest member
Wilson T

### 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.

### Which adblocker are you using?

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

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