VLOOKUP with IF

[steph]

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.

 

[Laura Cook]

You need quotes around UNCLASSIFIED


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

 

[Conan Kelly]

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

 

[Max]

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

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.

 

[Gord Dibben]

=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

 

[Tyro]

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]

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!

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

 

[steph]

Tyro, your response was exactly correct. Thank you for your help!
--
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
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
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.