IF/VLOOKUP formula question

[Diving_Dan]

Hi all,

Probably a really simple answer to my question.

I've written the below formula to give me a tel number in a cell if one is held on another sheet and to show no tel held if it isn't there. The final part of the formula works fine but if i hold a tel number is gives me #value ! error.

=IF(VLOOKUP(E5,'Hidden stuff'!L2:R1275,7,FALSE),"<>","NO TEL HELD")

What do I need to put in the [velue is true] portion of the formula to show me the result? Or do i need to do something a lot simpler?

As always thanks in advance

 

[DanteAmor]

Try this


=IFERROR(VLOOKUP(E5,'Hidden stuff'!L2:R1275,7,0),"NO TEL HELD")

 

[Diving_Dan]

I tried that formula before and it gives me 0 as the result.

I think my formula at the top will work but I just need to know what to put where i have put "<>". I would like that value to be returned.

 

[Diving_Dan]

Would I have to put another VLOOKUP as the [value is true] portion of the formula?

 

[DanteAmor]

You can perform the following exercise.
In cell E5 write the word "test". Now in cell L2 write the word "test", in cell R2 write the word "Hello".
The formula should return "Hello."
Change the word "test" in cell E5 to "xyz", the formula returns: "NO TEL HELD"

After those tests, perform the test with your data and tell me what you have in your cells and the result of the formula.

 

[Diving_Dan]

Fixed with the following.

=IF(VLOOKUP(E5,'Hidden stuff'!L2:R1275,7,FALSE)>0,(VLOOKUP(E5,'Hidden stuff'!L2:R1275,7,FALSE)),"NO TEL HELD")

 

[DanteAmor]

But you still have the problem if the data in cell E5 does not exist in the range, it sends you the error #N/A


It can be corrected with:

=IF(IFERROR(VLOOKUP(E5,'Hidden stuff'!L2:R1275,7,FALSE)>0,0),(VLOOKUP(E5,'Hidden stuff'!L2:R1275,7,FALSE)),"NO TEL HELD")

 

[Diving_Dan]

Yes i just found that out when i got rid of the data from E5. Thank you for that correction. The only problem i now have is if there isn't anything in E5 I get NO TEL HELD permanently showing.

I was hoping for the cell to be blank and then it only displays either NO TEL HELD or a tel number once I put data into E5?

 

[DanteAmor]

Try this

=IF(E5="","",IF(IFERROR(VLOOKUP(E5,'Hidden stuff'!L2:R1275,7,0)>0,0),(VLOOKUP(E5,'Hidden stuff'!L2:R1275,7,0)),"NO TEL HELD"))

 

[Diving_Dan]

Worked a treat, Thank you