Errors encountered when using If & Vlookup formulas

N

nikko

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)))
 
L

Luke M

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,'Opp
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))
 
N

nikko

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top