|
VLOOKUP statement too long
I have the following statement, as it stands it works fine, but I would like
to expand on it and have it lookup three more cells. The problem is that statement is then too long. I was wondering if anyone had any suggestions on how to make the statement shorter and still do what I need it to, or another way of accomplishing this. =IF(ISNA(VLOOKUP(' Form 2'!R31,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R31,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that " & CHAR(10) & CHAR(10) &("2. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R32,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R32,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that " & CHAR(10) & CHAR(10) &("3. ") &(' Form2 '!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R33,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R33,'Employee List'!F1:G132,2)) I would like to add: & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("4. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R34,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R34,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("5. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R35,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R35,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("6. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R36,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R36,'Employee List'!F1:G132,2)) Thanks in advance |
VLOOKUP statement too long
put parts of your formula on a hidden sheet in mulitple cells. Then use the
results to build your final output. or, use defined names to produced major parts of your formula and combine the results of the defined names (insert=Name=define). -- Regards, Tom Ogilvy "Tim" wrote in message ... I have the following statement, as it stands it works fine, but I would like to expand on it and have it lookup three more cells. The problem is that statement is then too long. I was wondering if anyone had any suggestions on how to make the statement shorter and still do what I need it to, or another way of accomplishing this. =IF(ISNA(VLOOKUP(' Form 2'!R31,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R31,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that " & CHAR(10) & CHAR(10) &("2. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R32,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R32,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that " & CHAR(10) & CHAR(10) &("3. ") &(' Form2 '!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R33,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R33,'Employee List'!F1:G132,2)) I would like to add: & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("4. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R34,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R34,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("5. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R35,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R35,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("6. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R36,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R36,'Employee List'!F1:G132,2)) Thanks in advance |
VLOOKUP statement too long
Tim,
Can you describe what you are attempt to do - it appears to be constructing a "message" - so we can see if there is better solution. One problem with complex formula is that in the (not too distant) future it will be difficult to recall/determine what you are trying to do. What are the LOOKUP fields/Values? "Tim" wrote: I have the following statement, as it stands it works fine, but I would like to expand on it and have it lookup three more cells. The problem is that statement is then too long. I was wondering if anyone had any suggestions on how to make the statement shorter and still do what I need it to, or another way of accomplishing this. =IF(ISNA(VLOOKUP(' Form 2'!R31,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R31,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that " & CHAR(10) & CHAR(10) &("2. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R32,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R32,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that " & CHAR(10) & CHAR(10) &("3. ") &(' Form2 '!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R33,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R33,'Employee List'!F1:G132,2)) I would like to add: & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("4. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R34,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R34,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("5. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R35,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R35,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("6. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R36,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R36,'Employee List'!F1:G132,2)) Thanks in advance |
VLOOKUP statement too long
Thanks Tom
I split the formula into two cells and then used a third cell to build it. I then use data validation to call the third cell and paste the results where I need it. Works the way I need it to. "Tom Ogilvy" wrote: put parts of your formula on a hidden sheet in mulitple cells. Then use the results to build your final output. or, use defined names to produced major parts of your formula and combine the results of the defined names (insert=Name=define). -- Regards, Tom Ogilvy "Tim" wrote in message ... I have the following statement, as it stands it works fine, but I would like to expand on it and have it lookup three more cells. The problem is that statement is then too long. I was wondering if anyone had any suggestions on how to make the statement shorter and still do what I need it to, or another way of accomplishing this. =IF(ISNA(VLOOKUP(' Form 2'!R31,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R31,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that " & CHAR(10) & CHAR(10) &("2. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R32,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R32,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that " & CHAR(10) & CHAR(10) &("3. ") &(' Form2 '!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R33,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R33,'Employee List'!F1:G132,2)) I would like to add: & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("4. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R34,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R34,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("5. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R35,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R35,'Employee List'!F1:G132,2)) & CHAR(10) & CHAR(10) & "and further that" & CHAR(10) & CHAR(10) &("6. ") &(' Form 2'!AF4&" "&' Form 2'!AY4&" "&' Form 2'!I4) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP(' Form 2'!R36,'Employee List'!F1:G132,2)),"",VLOOKUP(' Form 2'!R36,'Employee List'!F1:G132,2)) Thanks in advance |
| All times are GMT +1. The time now is 03:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com