IF/VLOOKUP formula question

Diving_Dan

Board Regular
Joined
Oct 20, 2019
Messages
77
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
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
Try this


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

Diving_Dan

Board Regular
Joined
Oct 20, 2019
Messages
77
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
Joined
Oct 20, 2019
Messages
77
Would I have to put another VLOOKUP as the [value is true] portion of the formula?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
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
Joined
Oct 20, 2019
Messages
77
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
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
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
Joined
Oct 20, 2019
Messages
77
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
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
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"))
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top