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

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


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

Sorry but that didn't work either.

"shail" wrote:

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

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

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

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

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

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

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

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