Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Worksheet Functions / February 2008

Tip: Looking for answers? Try searching our database.

VLOOKUP with IF

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
steph - 01 Feb 2008 23:39 GMT
I've scoured this site for the past hour and can't seem to use any of your
suggestions to get my formula to work.  I'm sure it's just a misplaced
character or something but can someone help?

If my VLOOKUP value is not found in a second workbook, I need to return the
word UNCLASSIFIED.  If the value is found, return the value 5 columns to the
right.

Here is my formula
equalIF(ISNA(VLOOKUP(C3,'[filename.xls]tabname'!$A:$F,5)),UNCLASSIFIED,VLOOKUP(C3,'[filename]tabname'!$A:$F,5))

I can get the VLOOKUP to work fine using:
equalVLOOKUP(C3,'[filename]tabname'!$A:$F,5,FALSE)
But I get tripped up when trying to add the IF statement to it.

Signature

Thanks so much!

Reply to this Message
Laura Cook - 01 Feb 2008 23:52 GMT
You need quotes around UNCLASSIFIED

=IF(ISNA(VLOOKUP(C3,'[filename.xls]tabname'!$A:$F,5)),"UNCLASSIFIED",VLOOKUP(C3,'[filename]tabname'!$A:$F,5))

> I've scoured this site for the past hour and can't seem to use any of your
> suggestions to get my formula to work.  I'm sure it's just a misplaced
[quoted text clipped - 12 lines]
> equalVLOOKUP(C3,'[filename]tabname'!$A:$F,5,FALSE)
> But I get tripped up when trying to add the IF statement to it.
Reply to this Message
Conan Kelly - 01 Feb 2008 23:55 GMT
steph,

I haven't tested this, but at first glance, your formula looks correct.
Just put quotes around UNCLASSIFIED:

=IF(ISNA(VLOOKUP(C3,'[filename.xls]tabname'!$A:$F,5)),"UNCLASSIFIED",VLOOKUP(C3,'[filename]tabname'!$A:$F,5))

HTH,

If not, reply and I'll look at it closer.

Conan

> I've scoured this site for the past hour and can't seem to use any of your
> suggestions to get my formula to work.  I'm sure it's just a misplaced
[quoted text clipped - 12 lines]
> equalVLOOKUP(C3,'[filename]tabname'!$A:$F,5,FALSE)
> But I get tripped up when trying to add the IF statement to it.
Reply to this Message
Max - 02 Feb 2008 00:00 GMT
Re this part in your attempt:
> .. $A:$F,5)),UNCLASSIFIED,VLOOKUP(..

Input double quotes around the return text: UNCLASSIFIED, viz:
.. $A:$F,5)),"UNCLASSIFIED",VLOOKUP(..

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I've scoured this site for the past hour and can't seem to use any of your
> suggestions to get my formula to work.  I'm sure it's just a misplaced
[quoted text clipped - 10 lines]
> equalVLOOKUP(C3,'[filename]tabname'!$A:$F,5,FALSE)
> But I get tripped up when trying to add the IF statement to it.
Reply to this Message
Gord Dibben - 02 Feb 2008 00:00 GMT
=IF(ISNA(VLOOKUP(C3,'[filename.xls]tabname'!$A:$F,5,FALSE)),"UNCLASSIFIED",VLOOKUP(C3,'[filename]tabname'!$A:$F,5,FALSE))

FALSE argument added and double quotes needed around UNCLASSIFIED

Gord Dibben  MS Excel MVP

>I've scoured this site for the past hour and can't seem to use any of your
>suggestions to get my formula to work.  I'm sure it's just a misplaced
[quoted text clipped - 10 lines]
>equalVLOOKUP(C3,'[filename]tabname'!$A:$F,5,FALSE)
>But I get tripped up when trying to add the IF statement to it.
Reply to this Message
steph - 04 Feb 2008 14:23 GMT
Gord--this worked perfectly!!  I had tried using quotes but not with the
FALSE argument--that was what tripped me up.

P.S.  (Sorry I didn't get back to you sooner but I just got back in to work
and am reveling in the Giants WIN!!).
Signature

Thanks so much!

> =IF(ISNA(VLOOKUP(C3,'[filename.xls]tabname'!$A:$F,5,FALSE)),"UNCLASSIFIED",VLOOKUP(C3,'[filename]tabname'!$A:$F,5,FALSE))
>
[quoted text clipped - 16 lines]
> >equalVLOOKUP(C3,'[filename]tabname'!$A:$F,5,FALSE)
> >But I get tripped up when trying to add the IF statement to it.
Reply to this Message
Tyro - 02 Feb 2008 00:05 GMT
You don't explain what "tripped up" means but I notice that the word
UNCLASSIFIED has to be in quotes. The second thing I see is that in the
original formula your range_lookup is FALSE, return an exact match,  but not
in the formula with the IF. So the range_lookup defaults to TRUE - return an
exact or approximate match. So, if you want FALSE:

=IF(ISNA(VLOOKUP(C3,'[filename.xls]tabname'!$A:$F,5,FALSE)),"UNCLASSIFIED",VLOOKUP(C3,'[filename]tabname'!$A:$F,5,FALSE))

Tyro

> I've scoured this site for the past hour and can't seem to use any of your
> suggestions to get my formula to work.  I'm sure it's just a misplaced
[quoted text clipped - 12 lines]
> equalVLOOKUP(C3,'[filename]tabname'!$A:$F,5,FALSE)
> But I get tripped up when trying to add the IF statement to it.
Reply to this Message
steph - 04 Feb 2008 14:26 GMT
Tyro, your response was exactly correct.  Thank you for your help!
Signature

Thanks so much!

> You don't explain what "tripped up" means but I notice that the word
> UNCLASSIFIED has to be in quotes. The second thing I see is that in the
[quoted text clipped - 22 lines]
> > equalVLOOKUP(C3,'[filename]tabname'!$A:$F,5,FALSE)
> > But I get tripped up when trying to add the IF statement to it.
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.