VLOOKUP Question

[Cuzzaa]

Hi everyone

I am using the code below to do a vlookup on a product code entered on sheet 'Dashboard', and then matching this product code on a separate sheet and pulling the matching cost through on 'Equipment Cost Lookup'.

The code I am using below works fine, but what I am trying to achieve is to update the code to include an if statement so that if it cannot match the product code in cell G46 to a product code in the table being looked up in 'Equipment Cost Lookup'!C7:E100' then I want to show text 'NOT FOUND' in cell J46 rather than it just staying blank as it currently is if it cannot match the product code in the VLOOKUP.

=IFERROR(VLOOKUP(G46,'Equipment Cost Lookup'!C7:E100,3,FALSE),0)

Is anyone able to help please? I hope this is a simple amendment but I cannot get my head around what code I need to add here.

Thank you in advance.

 

[Trebor76]

Hi Cuzzaa,

I'm not sure if you mean VBA code or a standard worksheet formula

Assuming you're after the later try this (just need to change the zero if it errors out to what text you want):

=IFERROR(VLOOKUP(G46,'Equipment Cost Lookup'!C7:E100,3,FALSE),"Not Found")

Regards,

Robert

 

[Cuzzaa]

Hi Cuzzaa,

I'm not sure if you mean VBA code or a standard worksheet formula

Assuming you're after the later try this (just need to change the zero if it errors out to what text you want):

=IFERROR(VLOOKUP(G46,'Equipment Cost Lookup'!C7:E100,3,FALSE),"Not Found")

Regards,

Robert

Thank you for your help, Robert.

If I change the false return value to Not Found that works, but the only thing is that I only need this to return Not Found if Cell G46 is also NOT blank, i.e. if there is text in the product code. Is there a way to add this check in the code below my friend?

=IFERROR(VLOOKUP(G46,'Equipment Cost Lookup'!C7:E100,3,FALSE),"Not Found")

Thank you once again for your assistance!

 

[Sanjeev1976]

You can use :

=IF(ISBLANK(G46),0,IFERROR(VLOOKUP(G46,'Equipment Cost Lookup'!C7:E100,3,FALSE),"Not Found"))

 

[Cuzzaa]

You can use :

=IF(ISBLANK(G46),0,IFERROR(VLOOKUP(G46,'Equipment Cost Lookup'!C7:E100,3,FALSE),"Not Found"))

Thank you Sanjeev, that's great!

Can I ask another question...

How can I add one more condition within the below formula to check that G46 is NOT blank AND I46 contains a number greater than 0 (but ignores text) then return Not Found if my VLOOKUP can't match the data in G46? Is that possible?

=IF(ISBLANK(G46),0,IFERROR(VLOOKUP(G46,'Equipment Cost Lookup'!C7:E100,3,FALSE),"Not Found"))

[/QUOTE]

 

[Sanjeev1976]

=IF(ISBLANK(G46),0,IFERROR(VLOOKUP(G46,'Equipment Cost Lookup'!C7:E100,3,FALSE),IF(I46>0,"Not Found",""))

 

[Cuzzaa]

=IF(ISBLANK(G46),0,IFERROR(VLOOKUP(G46,'Equipment Cost Lookup'!C7:E100,3,FALSE),IF(I46>0,"Not Found",""))

Thanks for your help! I have tried the code about but when entering I am presented with a error saying it looks like there's a typo and is trying to auto fix it (but when I click YES to auto fix formula it doesn't work)... please could you check this for me?

 

[Cuzzaa]

=IF(ISBLANK(G46),0,IFERROR(VLOOKUP(G46,'Equipment Cost Lookup'!C7:E100,3,FALSE),IF(I46>0,"Not Found",""))

Hi

Alternatively, what would be even better is instead of checking if I46 contains a number larger than 0, is there a way just to check if I46 contains a NUMBER not text then print Not Found if I46 contains a number but the product code in G46 is not found via the VLOOKUP? Does that make sense?

 

[Cuzzaa]

Thanks for your help! I have tried the code about but when entering I am presented with a error saying it looks like there's a typo and is trying to auto fix it (but when I click YES to auto fix formula it doesn't work)... please could you check this for me?

Sanjeev,

I believe the below code works but the issue is that if cell I46 contains text then it still prints NOT FOUND, but I only need this to print NOT FOUND if cell I46 contains a NUMBER greater than 0 and ignores any text in this cell - is this possible?

Thank you so much in advance

 

[Cuzzaa]

Sanjeev,

I believe the below code works but the issue is that if cell I46 contains text then it still prints NOT FOUND, but I only need this to print NOT FOUND if cell I46 contains a NUMBER greater than 0 and ignores any text in this cell - is this possible?

Thank you so much in advance

Nevermind, I managed to get this working.

Thanks again everyone for your help.