Can a single cell be concatenated?

K

kgoshia

Board Regular
Joined
Mar 1, 2010
Messages
52
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)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))),)
 
Sort by date Sort by votes
WarfritLive said:
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")
Click to expand...

The formula above does not work???
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sorry, the match array should be column B, not A.
Try this :

=IFERROR(VLOOKUP(MATCH(E5,'Minimum Creepage 2N'!B6:B26),'Minimum Creepage 2N'!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),FALSE),"There's an error, yo")
 
Upvote 0
That is so awesome :) I have one issue to still resolve, the formula does not work for voltages in between to cell's. For example is the user enters 11 volts in cell E5 the calculation needs to determine it is between 10 and 12.5 volts, the same would go for 20-30 and so on.

Keith

WarfritLive said:
Sorry, the match array should be column B, not A.
Try this :

=IFERROR(VLOOKUP(MATCH(E5,'Minimum Creepage 2N'!B6:B26),'Minimum Creepage 2N'!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),FALSE),"There's an error, yo")
Click to expand...
 
Upvote 0
Sorry, I was not aware you needed 'in between' values.
Question: What should the result be for 11 volts? 12 volts? Does it use the values for the lower one (10) or the higher (12.5)?
 
Upvote 0
If I was to enter the voltage value of 11 volts or 12 volts it would default to the value in column B7 that equals 12.5 Volt. my calculation are up to and including the voltage at each cell in Column B.


WarfritLive said:
Sorry, I was not aware you needed 'in between' values.
Question: What should the result be for 11 volts? 12 volts? Does it use the values for the lower one (10) or the higher (12.5)?
Click to expand...
 
Upvote 0
=IFERROR(VLOOKUP(IF(E5<10.001,1,MATCH(E5-0.001, 'Minimum Creepage 2N'!B6:B26,1)+1),'Minimum Creepage 2N'!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),FALSE),"There's an error, yo")

Here, this should work.
You'll have to test it yourself as I'm away from my PC, sorry
 
Upvote 0
Amazing! that did the trick, so so less cumbersome.:)

WarfritLive said:
=IFERROR(VLOOKUP(IF(E5<10.001,1,MATCH(E5-0.001, 'Minimum Creepage 2N'!B6:B26,1)+1),'Minimum Creepage 2N'!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),FALSE),"There's an error, yo")

Here, this should work.
You'll have to test it yourself as I'm away from my PC, sorry
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Pivot Table Sorting Dates Out of Order
Replies
3
Views
708
awoohaw
awoohaw
M
  • Question
Parent/Child visual help
Replies
1
Views
106
Peter_SSs
Peter_SSs
A
  • Solved
distrubuting amounts from textbox on user form to column inside sheet
Replies
2
Views
154
abdo meghari
A
S
  • Solved
Extract Comma-Separated Matches Based on Multiple Criteria and Order Preservation
2
Replies
10
Views
155
sobuj53
S
C
  • Solved
If VLOOKUP is @NA check Table 2
Replies
9
Views
432
Joe4
Joe4

Forum statistics

Threads
1,214,520
Messages
6,120,016
Members
448,936
Latest member
almerpogi

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