| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning text from nested IF and Vlookup statements
I am trying to return a text statement using nested IF statements. In order
to find the value in the IF statements, I have to use lookups. Example: Find out if a site is open, scheduled to open or neither I have three spreadsheets: All Sites, Open Sites, Pending Sites. I've inserted a column in All sites to the left of column SiteNumber called status. I'd like status to be Open, Pending or Other. My formula entered is: =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending",IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE) ,"Open",Other)) I get replies back correctly for Pending but #N/A for any other sites. Any help is appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning text from nested IF and Vlookup statements
Hi Patricia,
Have you tried - =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending",IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE) ,"Open","Other")) Other to be in the double quotes too? Do let me know if this works Thanks Shail Patricia wrote: I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups. Example: Find out if a site is open, scheduled to open or neither I have three spreadsheets: All Sites, Open Sites, Pending Sites. I've inserted a column in All sites to the left of column SiteNumber called status. I'd like status to be Open, Pending or Other. My formula entered is: =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending",IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE) ,"Open",Other)) I get replies back correctly for Pending but #N/A for any other sites. Any help is appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning text from nested IF and Vlookup statements
Shail,
Sorry but that didn't work either. "shail" wrote: Hi Patricia, Have you tried - =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending",IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE) ,"Open","Other")) Other to be in the double quotes too? Do let me know if this works Thanks Shail Patricia wrote: I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups. Example: Find out if a site is open, scheduled to open or neither I have three spreadsheets: All Sites, Open Sites, Pending Sites. I've inserted a column in All sites to the left of column SiteNumber called status. I'd like status to be Open, Pending or Other. My formula entered is: =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending",IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE) ,"Open",Other)) I get replies back correctly for Pending but #N/A for any other sites. Any help is appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning text from nested IF and Vlookup statements
I'm sorry I should be more clear. It appears to be an issue where the second
if statement takes #N/A for the value instead of the actual cell value as referenced if not found on the first tab. "Patricia" wrote: Shail, Sorry but that didn't work either. "shail" wrote: Hi Patricia, Have you tried - =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending",IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE) ,"Open","Other")) Other to be in the double quotes too? Do let me know if this works Thanks Shail Patricia wrote: I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups. Example: Find out if a site is open, scheduled to open or neither I have three spreadsheets: All Sites, Open Sites, Pending Sites. I've inserted a column in All sites to the left of column SiteNumber called status. I'd like status to be Open, Pending or Other. My formula entered is: =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending",IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE) ,"Open",Other)) I get replies back correctly for Pending but #N/A for any other sites. Any help is appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning text from nested IF and Vlookup statements
hi again Patricia,
You might be VLOOKUPing it incorrectly. =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE) 1 could never be VLOOKUPed in normal cases. 1 is the column number 1, 2 will be column number 2 and so on from where you have selected the range. Try out giving the correct column number and tell me the result Thanks again Shail Patricia wrote: Shail, Sorry but that didn't work either. "shail" wrote: Hi Patricia, Have you tried - =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending",IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE) ,"Open","Other")) Other to be in the double quotes too? Do let me know if this works Thanks Shail Patricia wrote: I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups. Example: Find out if a site is open, scheduled to open or neither I have three spreadsheets: All Sites, Open Sites, Pending Sites. I've inserted a column in All sites to the left of column SiteNumber called status. I'd like status to be Open, Pending or Other. My formula entered is: =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending",IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE) ,"Open",Other)) I get replies back correctly for Pending but #N/A for any other sites. Any help is appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning text from nested IF and Vlookup statements
Shail,
The column number is correct. The first column in the other spreadsheets is Store number which is where the vlookup should check. Columns a (1) Store number, (2) Address, (3) City, (4) State, (5) Zip Code or similar but the Store number is always first so I can do other lookups for sales, etc. Patricia "shail" wrote: hi again Patricia, You might be VLOOKUPing it incorrectly. =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE) 1 could never be VLOOKUPed in normal cases. 1 is the column number 1, 2 will be column number 2 and so on from where you have selected the range. Try out giving the correct column number and tell me the result Thanks again Shail Patricia wrote: Shail, Sorry but that didn't work either. "shail" wrote: Hi Patricia, Have you tried - =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending",IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE) ,"Open","Other")) Other to be in the double quotes too? Do let me know if this works Thanks Shail Patricia wrote: I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups. Example: Find out if a site is open, scheduled to open or neither I have three spreadsheets: All Sites, Open Sites, Pending Sites. I've inserted a column in All sites to the left of column SiteNumber called status. I'd like status to be Open, Pending or Other. My formula entered is: =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending",IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE) ,"Open",Other)) I get replies back correctly for Pending but #N/A for any other sites. Any help is appreciated. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning text from nested IF and Vlookup statements
Hi again Patricia,
VLOOKUP(B3,Pending_Sites!A2:E118,1,FALSE) This is what your 1st VLOOKUP section. 1. "B3" is what you will match 2. "Pending_Sites!A2:E118" is what your range where B3 will search for its match 3. "1" is the column number from where you will get your result 4. It may be "TRUE" or "FALSE" Your mistake The range must start from the column you are VLOOKUP so it will be "Pending_Sites!B3:E118" and not "Pending_Sites!A2:E118". Which means B3 must be the leftest column. So, B3 will be your 1st column, C3 will be 2nd and so on. This is for the normal case. Where the LOOKUP works in right hand direction. And it is a simple process. Try to correct the function and if possible rearrange the table. Thanks Shail Patricia wrote: Shail, The column number is correct. The first column in the other spreadsheets is Store number which is where the vlookup should check. Columns a (1) Store number, (2) Address, (3) City, (4) State, (5) Zip Code or similar but the Store number is always first so I can do other lookups for sales, etc. Patricia |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning text from nested IF and Vlookup statements
Shail,
I'm appreciative of your help but I don't think you're seeing where I'm having the problem. In your recent post, you pointed out where the lookup information comes from in the first part of my formula. As mentioned in my first post, the formula returns the correct reply for the pending sites but putting "Pending" in the cell. The problem arises because the second part seems to be lapping from Pending and not from my Sites spreadsheet as orginally written. =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending" returns "Pending" in the cell The next section IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE),"Open", "Other")) is returning #N/A because it is checking 'Pending_Sites' and as it isn't finding them is returnin#N/A instead of my orginal cell B3. I know where the problem is, I just don't know how to fix it. I've tried from other posts using ISNA and ISERROR but that doesn't seem to work. I need to know how to fix the second IF statement to check the value in B3 instead of the lookup from Pending. Patricia "shail" wrote Hi again Patricia, VLOOKUP(B3,Pending_Sites!A2:E118,1,FALSE) This is what your 1st VLOOKUP section. 1. "B3" is what you will match 2. "Pending_Sites!A2:E118" is what your range where B3 will search for its match 3. "1" is the column number from where you will get your result 4. It may be "TRUE" or "FALSE" Your mistake The range must start from the column you are VLOOKUP so it will be "Pending_Sites!B3:E118" and not "Pending_Sites!A2:E118". Which means B3 must be the leftest column. So, B3 will be your 1st column, C3 will be 2nd and so on. This is for the normal case. Where the LOOKUP works in right hand direction. And it is a simple process. Try to correct the function and if possible rearrange the table. Thanks Shail Patricia wrote: Shail, The column number is correct. The first column in the other spreadsheets is Store number which is where the vlookup should check. Columns a (1) Store number, (2) Address, (3) City, (4) State, (5) Zip Code or similar but the Store number is always first so I can do other lookups for sales, etc. Patricia |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning text from nested IF and Vlookup statements
Patricia,
Can you send me the spreadsheet over email thanks Shail Patricia wrote: Shail, I'm appreciative of your help but I don't think you're seeing where I'm having the problem. In your recent post, you pointed out where the lookup information comes from in the first part of my formula. As mentioned in my first post, the formula returns the correct reply for the pending sites but putting "Pending" in the cell. The problem arises because the second part seems to be lapping from Pending and not from my Sites spreadsheet as orginally written. =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending" returns "Pending" in the cell The next section IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE),"Open", "Other")) is returning #N/A because it is checking 'Pending_Sites' and as it isn't finding them is returnin#N/A instead of my orginal cell B3. I know where the problem is, I just don't know how to fix it. I've tried from other posts using ISNA and ISERROR but that doesn't seem to work. I need to know how to fix the second IF statement to check the value in B3 instead of the lookup from Pending. Patricia "shail" wrote Hi again Patricia, VLOOKUP(B3,Pending_Sites!A2:E118,1,FALSE) This is what your 1st VLOOKUP section. 1. "B3" is what you will match 2. "Pending_Sites!A2:E118" is what your range where B3 will search for its match 3. "1" is the column number from where you will get your result 4. It may be "TRUE" or "FALSE" Your mistake The range must start from the column you are VLOOKUP so it will be "Pending_Sites!B3:E118" and not "Pending_Sites!A2:E118". Which means B3 must be the leftest column. So, B3 will be your 1st column, C3 will be 2nd and so on. This is for the normal case. Where the LOOKUP works in right hand direction. And it is a simple process. Try to correct the function and if possible rearrange the table. Thanks Shail Patricia wrote: Shail, The column number is correct. The first column in the other spreadsheets is Store number which is where the vlookup should check. Columns a (1) Store number, (2) Address, (3) City, (4) State, (5) Zip Code or similar but the Store number is always first so I can do other lookups for sales, etc. Patricia |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning text from nested IF and Vlookup statements
Patricia,
Again for the second section you have used the same thing, it will certainly reture #NA IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE),"Open", "Other")) because you made it to look and find towards it left side whereas the VLOOKUP should find its right hand side. As you are saying it must be overlaping Pending_Sites. No, it can't as you have linked it by 'Open_Sites'!, so it is where you want it to be. Just change your formula. Just try it. The B3 must be the leftmost column of the range you have selected and not in between the range you mentioned ie. A1:E63 or A1:E118. Thanks Shail Patricia wrote: Shail, I'm appreciative of your help but I don't think you're seeing where I'm having the problem. In your recent post, you pointed out where the lookup information comes from in the first part of my formula. As mentioned in my first post, the formula returns the correct reply for the pending sites but putting "Pending" in the cell. The problem arises because the second part seems to be lapping from Pending and not from my Sites spreadsheet as orginally written. =IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"P ending" returns "Pending" in the cell The next section IF(VLOOKUP(B3,'Open_Sites'!A1:E63,1,FALSE),"Open", "Other")) is returning #N/A because it is checking 'Pending_Sites' and as it isn't finding them is returnin#N/A instead of my orginal cell B3. I know where the problem is, I just don't know how to fix it. I've tried from other posts using ISNA and ISERROR but that doesn't seem to work. I need to know how to fix the second IF statement to check the value in B3 instead of the lookup from Pending. Patricia "shail" wrote Hi again Patricia, VLOOKUP(B3,Pending_Sites!A2:E118,1,FALSE) This is what your 1st VLOOKUP section. 1. "B3" is what you will match 2. "Pending_Sites!A2:E118" is what your range where B3 will search for its match 3. "1" is the column number from where you will get your result 4. It may be "TRUE" or "FALSE" Your mistake The range must start from the column you are VLOOKUP so it will be "Pending_Sites!B3:E118" and not "Pending_Sites!A2:E118". Which means B3 must be the leftest column. So, B3 will be your 1st column, C3 will be 2nd and so on. This is for the normal case. Where the LOOKUP works in right hand direction. And it is a simple process. Try to correct the function and if possible rearrange the table. Thanks Shail Patricia wrote: Shail, The column number is correct. The first column in the other spreadsheets is Store number which is where the vlookup should check. Columns a (1) Store number, (2) Address, (3) City, (4) State, (5) Zip Code or similar but the Store number is always first so I can do other lookups for sales, etc. Patricia |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning text from nested IF and Vlookup statements
Hi Patricia,
Have you got my email? I have made the change in the function. VLOOKUP was not needed here. I used =IF(OR(B3='Sites Targeted'!A$3:A$15),"Targeted",IF(OR(B3='Sites Supplied'!A$3:A$9),"Supplied","None")) Enter it as array function. Thanks shail |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup not returning full field value (text) | Excel Worksheet Functions | |||
| Complex Vlookup and List Validation and Nested IF statements | Excel Worksheet Functions | |||
| Vlookup of list with text and number | Excel Discussion (Misc queries) | |||
| Numbers stored as text causes problem with VLOOKUP | Excel Worksheet Functions | |||
| Combining Text and Date for VLOOKUP | Excel Worksheet Functions | |||
Linear Mode
