# Revised IF statement question

#### shapenote

##### Board Regular
I need to modify the below if statement.

=IFERROR(IF(\$C2="X", VLOOKUP(\$A2,Manuals, 2, 0), IF(\$C2="", VLOOKUP(\$A2, Kickout, 22, 0))), VLOOKUP(\$A2, Evaluations, 9, 0))

1) I need to add a criteria to the last part of the statement that says if \$C2 is "" and VLOOKUP(\$A2, Kickout, 22, 0) = "", then do the VLOOKUP(\$A2, Evaluations, 9, 0).

2) Also, how can I modify the whole formula to never return a 0, but a blank? Currently if any of the cells it is looking up locates a blank space I am getting a 0 return.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
I think this will take care of the first part of your problem. I'm unable to test it properly due to your named ranges, though:

Code:
``=IFERROR(IF(\$C2="X", VLOOKUP(\$A2,Manuals, 2, 0), IF(\$C2="",VLOOKUP(\$A2, Kickout, 22, 0),IF(AND(\$C2="",VLOOKUP(\$A2,Kickout,22,0)=""),VLOOKUP(\$A2,Evaluations,9,0)))), VLOOKUP(\$A2, Evaluations, 9, 0))``

Hope this helps.

It didn't work. It still returned me a value of 0 instead of going to the last criteria and returning me the value in the Manuals range --- which that range does contain a value.

Basically I am trying to get one of three returns -- in this order of precedence:

#1 -- if the info is on my Manuals tab (range) -- then I want that data

#2 -- if not on Manuals tab (range), look on the Kickout tab (range) and return that data

#3 -- if not on Manuals and not on Kickout (OR it may be on Kickout but blank) then return the data listed on the Evaluations tab (range).

Still could use some help on this issue. Any thoughts on how to get past my formula on the second vlookup if it finds a blank and returns a zero? I need it to move to the three part of the equation where if C2 is blank and the Kickout data range has nothing, then look up on the Evaluations data range.

Replies
8
Views
340
Replies
7
Views
161
Replies
1
Views
352
Replies
3
Views
194
Replies
7
Views
758

1,203,465
Messages
6,055,577
Members
444,799
Latest member
CraigCrowhurst

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