Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

error with Vlookup

Posted on 2014-09-08
4
Medium Priority
?
150 Views
Last Modified: 2014-09-08
I am doing a VLookup with an IF an the result it is returning is True. What I am trying to dai is say

If R2 is = No Match Lookup AK2 in the range A:AB column 5 if found return the answer but if it's not there I want it to return Not Found

=ISERROR(IF(R154="No Match",VLOOKUP(AK154,X:AB,5,0),"Not Found"))

What am I doing wrong?

Thanks in advance
0
Comment
Question by:Jagwarman
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:helpfinder
ID: 40309806
but ISERROR formula evaluates value inside formula and gives you TRUE or FALSE.
what if you try just:
=IF(R154="No Match",VLOOKUP(AK154,X:AB,5,0),"Not Found")

or you can attach excel sample

PS: also could be:
=IF(R154="No Match",VLOOKUP(AK154,$X:$AB,5,0),"Not Found")
0
 

Author Comment

by:Jagwarman
ID: 40309855
helpfinder

That returns N/A
0
 
LVL 19

Accepted Solution

by:
helpfinder earned 2000 total points
ID: 40309884
then it means in R154 is "No Match" and formula continues with true argument (VLOOKUP). VLOOKUP does not find match so returns N/A. This is how I see that formula and interprete your result.
If you want to have "Not Found" in case R154 is not "No Match" (this you have) and something else (e.g. "Nth found") if VLOOKUP does not find a match try to use this formula:
=iferror(IF(R154="No Match",VLOOKUP(AK154,X:AB,5,0),"Not Found"),"Nth found")
0
 

Author Comment

by:Jagwarman
ID: 40309920
I changed it to

=IFERROR(IF(R154="No Match",VLOOKUP(AK154,X:AB,5,0),""),"Not Found")

and it is exactly what I needed

Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question