Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 195
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 195
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 195
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 195
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 195
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 195
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup not returning full field value (text) JohnC Excel Worksheet Functions 0 June 27th 06 12:03 PM
Complex Vlookup and List Validation and Nested IF statements Bobby Excel Worksheet Functions 2 March 9th 06 06:37 PM
Vlookup of list with text and number robertjtucker Excel Discussion (Misc queries) 2 February 8th 06 06:23 PM
Numbers stored as text causes problem with VLOOKUP bpeltzer Excel Worksheet Functions 0 February 4th 06 09:07 PM
Combining Text and Date for VLOOKUP biggymismyname Excel Worksheet Functions 2 December 5th 05 05:01 PM


All times are GMT +1. The time now is 01:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"