VLOOKUP with an IF

[Draccusfly]

Hi

I am trying to tidy up a worksheet that has a vlookup reference to a second worksheet. The formula at present is:

=VLOOKUP($J:$J,'DATA ENTRY'!$A$3:$H$1000,3,FALSE)

The primary sheet has a data connection on columns J:X which updates on a daily basis, reference data is then placed on the data entry sheet which then populates data back to the primary sheet on columns A:I based on the result of the lookup. As data is removed or changed in columns J:X columns A:I will update and line up across columns A:X

What I want to do is try and use an IF statement so that if the resultant value of the lookup is 0, #N/A or 00-Jan then nothing should be displayed in that cell.

at this stage the workbook works fine just looks abit untidy

Drac

 

[hstaubyn]

Why not use conditional formatting? You could have a rule that says if the cell equals any of those values, the colour of the cell contents is white.

 

[Draccusfly]

Thanks for the reply..

I had considered using conditional formatting, but it seems that as the "#N/A" entries are results of blank entries in the data entry sheet, which CF seems to struggle with.

Drac

 

[Bunglebogs]

=if(or(isna(VLOOKUP($J:$J,'DATA ENTRY'!$A$3:$H$1000,3,FALSE)),VLOOKUP($J:$J,'DATA ENTRY'!$A$3:$H$1000,3,FALSE)=0),"",VLOOKUP($J:$J,'DATA ENTRY'!$A$3:$H$1000,3,FALSE))

 

[Draccusfly]

Thanks for the reply, worked a treat. I found that i didn't need the whole formula though and used:

=IF(ISNA(VLOOKUP($J:$J,'DATA ENTRY'!$A$3:$K$1000,8,FALSE)),"",VLOOKUP($J:$J,'DATA ENTRY'!$A$3:$K$1000,8,FALSE))

Instead of:

=if(or(isna(VLOOKUP($J:$J,'DATA ENTRY'!$A$3:$H$1000,3,FALSE)),VLOOKUP($J:$J,'DATA ENTRY'!$A$3:$H$1000,3,FALSE)=0),"",VLOOKUP($J:$J,'DATA ENTRY'!$A$3:$H$1000,3,FALSE))

Which seemed to do exactly the same thing. However Neither got rid of #VALUE that occurs where I have populated cells with the formula that have no reference to draw data from. Might just have to copy and paste formula into extra rows as and when required.

Drac

 

[Bunglebogs]

The OR clause checked for #N/A errors and zero value (as per the requirement in your original post ) If you don't need to remove zero values, you're good to go!

The #VALUE error is probably due to you performing an arithmetic operation on a non-numeric. You may need to add some If(ISNUMBER... conditions to your formulae.

 

[Draccusfly]

I realised why I could remove the Zero value part of the formula, I had unticked "show zero values" in advanced options

I'll take a look at the =if(isnumber... option

Cheers for the help

Drac

 

[jasonb75]

Another way

=IF(COUNTIF('DATA ENTRY'!$A$3:$A$1000,$J2),VLOOKUP($J2,'DATA ENTRY'!$A$3:$K$1000,8,FALSE),"")

 

[bennobes]

I apologise in advance but I have an error on this formula. The formula returns blank cell as I intended if the index cell is blank but returns a #value error if the data in the index column cell has data in. The data in the index column cell is in date format. The formula I'm using is

=IF(ISBLANK(VLOOKUP(B3,'New Starter Monitor & Spot'!$B$79:$N$88,3,FALSE)),"",VLOOKUP('New Starter Monitor & Spot'!$B$79:$N$88,3,FALSE))

The ISBLANK successfully removed unwanted data reurned in cell when index cell was blank. I need to return blank from blank index cell otherwise conditional fomratting in formula cell becomes irrellevant because cell gives random date?

HELP!!!!

 

[hstaubyn]

I can't honestly say I follow you entirely, but...

could it be that you are returning "" as the value and not 'blankness'? It is impossible (I think) for a formula to return a blank value - excel does not see "" as blank.

Could you perhaps explain a little clearer what you mean by "index column cell"? That is confusing me a bit.