Errors encountered when using If & Vlookup formulas
Hi
Need some help here.. Trying to have the formula to lookup J2 field in the Opp Summary spreadsheet and return Col I value. If Col I value is blank, then return the value in Col J. If Col J value = direct, then show "-" encountered #Name error for below formula ... any help / advice appreciated. thanks! IF(ISBLANK(VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,10,FALSE)), VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE), IF(VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE)="Direct", "-", VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE))) -- nikko |
Errors encountered when using If & Vlookup formulas
Can you post sample file so that it will be easy to sort out problem -- mubashir aziz ------------------------------------------------------------------------ mubashir aziz's Profile: http://www.thecodecage.com/forumz/member.php?userid=237 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86726 |
Errors encountered when using If & Vlookup formulas
You've got your True/False conditions swapped, and you're calling out columns
J, then I, opposite of what you say in example. What you asked in example: =IF(ISBLANK(VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE)),IF(VLOOKUP($J$2,'O pp Summary'!$A$1:$N$9996,10,FALSE)="Direct", "-",VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,10,FALSE)),VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "nikko" wrote: Hi Need some help here.. Trying to have the formula to lookup J2 field in the Opp Summary spreadsheet and return Col I value. If Col I value is blank, then return the value in Col J. If Col J value = direct, then show "-" encountered #Name error for below formula ... any help / advice appreciated. thanks! IF(ISBLANK(VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,10,FALSE)), VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE), IF(VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE)="Direct", "-", VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE))) -- nikko |
Errors encountered when using If & Vlookup formulas
Sorry, it should be calling out Col J and if Col J is blank, then return the
value in Col I.. If Col J = blank, then reflect the value in Col I If COl I = direct, then reflect "-" With below formula, even though my Column J has got text info, it's reflecting the value in Col I IF(ISBLANK(VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,10,FALSE)),IF(VLOOKUP($J$2,' Opp Summary'!$A$1:$N$9996,9,FALSE)="Direct", "-",VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE)),VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE)) -- nikko "Luke M" wrote: You've got your True/False conditions swapped, and you're calling out columns J, then I, opposite of what you say in example. What you asked in example: =IF(ISBLANK(VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE)),IF(VLOOKUP($J$2,'O pp Summary'!$A$1:$N$9996,10,FALSE)="Direct", "-",VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,10,FALSE)),VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "nikko" wrote: Hi Need some help here.. Trying to have the formula to lookup J2 field in the Opp Summary spreadsheet and return Col I value. If Col I value is blank, then return the value in Col J. If Col J value = direct, then show "-" encountered #Name error for below formula ... any help / advice appreciated. thanks! IF(ISBLANK(VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,10,FALSE)), VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE), IF(VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE)="Direct", "-", VLOOKUP($J$2,'Opp Summary'!$A$1:$N$9996,9,FALSE))) -- nikko |
All times are GMT +1. The time now is 09:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com