Solved

vlookup help

Posted on 2015-02-04
3
94 Views
Last Modified: 2015-02-23
folks

I have a vlookup

VLOOKUP(A1,Sheet2!B1:C6,2,FALSE)

how can i instead of showing N/A when the condition is false rather state 'NOT FOUND'

all help will do
0
Comment
Question by:rutgermons
3 Comments
 
LVL 18

Accepted Solution

by:
SimonAdept earned 500 total points
Comment Utility
=IFERROR(VLOOKUP(A1,Sheet2!B1:C6,2,FALSE),"NOT FOUND")
0
 
LVL 4

Expert Comment

by:Monika Bharti
Comment Utility
Hi,

To return N/A when the condition is false with any error from the VLOOKUP (e.g. including if B2 is populated but that value isn't found by the VLOOKUP) you can use IFERROR function, if you have Excel 2007.

=IFERROR(VLOOKUP(B2,Index!A1:B12,2,FALSE),"N/A")

Open in new window


In earlier versions you need to repeat the VLOOKUP, e.g.
=IF(ISNA(VLOOKUP(B2,Index!A1:B12,2,FALSE)),"N/A",VLOOKUP(B2,Index!A1:B12,2,FALSE))

Open in new window


Note: Replace N/A if you want to change this in other string which you want.
0
 

Expert Comment

by:Matt Dunn
Comment Utility
You might be expecting that not all of your search values are going to return something from the search table, in which case you can use the iferror function.

It works like this

= iferror (YourVlookupFormula, WhatToSayInsteadOf#N/A)

Here’s an example

=iferror(vlookup(D3,A:C,3,false), “Not Found”)

Or if you would rather it was just blank then instead of having No Value Found, just have the two sets of inverted commas, like this

=iferror(vlookup(D3,A:C,3,false), “”)

(Source: http://www.excelvlookuphelp.com/i-dont-want-to-see-na-if-the-lookup-value-isnt-found/)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now