Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |