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