# IF/VLOOKUP formula question

#### Diving_Dan

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

##### Well-known Member
Try this

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

Last edited:

#### Diving_Dan

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

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

#### DanteAmor

##### Well-known Member
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

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

##### Well-known Member
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

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

##### Well-known Member
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

##### Board Regular
Worked a treat, Thank you

1,082,367
Messages
5,365,028
Members
400,819
Latest member
Gossow

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...