VLOOKUP with IF
 

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.

--
Thanks so much!

VLOOKUP with IF
 

You need quotes around UNCLASSIFIED


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


"steph" wrote in message
...
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.

--
Thanks so much!

VLOOKUP with IF
 

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





"steph" wrote in message
...
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.

--
Thanks so much!

VLOOKUP with IF
 

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(..

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"steph" wrote:
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.

--
Thanks so much!

VLOOKUP with IF
 

=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

On Fri, 1 Feb 2008 15:39:03 -0800, steph
wrote:

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.

VLOOKUP with IF
 

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


"steph" wrote in message
...
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.

--
Thanks so much!

VLOOKUP with IF
 

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!!).
--
Thanks so much!


"Gord Dibben" wrote:


=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

On Fri, 1 Feb 2008 15:39:03 -0800, steph
wrote:

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.

VLOOKUP with IF
 

Tyro, your response was exactly correct. Thank you for your help!
--
Thanks so much!


"Tyro" wrote:

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


"steph" wrote in message
...
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.

--
Thanks so much!