Returning text from nested IF and Vlookup statements

[Guest]

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),"Pending",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.

 

[shail]

Hi Patricia,

Have you tried -

=IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"Pending",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

 

[Guest]

Shail,

Sorry but that didn't work either.

Hi Patricia,

Have you tried -

=IF(VLOOKUP(B3,'Pending_Sites'!A2:E118,1,FALSE),"Pending",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

 

[Guest]

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.

 

[shail]

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

 

[Guest]

Shail,

The column number is correct. The first column in the other spreadsheets is
Store number which is where the vlookup should check.
Columns are: (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]

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

 

[Guest]

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),"Pending" 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]

Patricia,

Can you send me the spreadsheet over email

thanks

Shail

 

[shail]

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

 

[shail]

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