I am using VLOOKUP() to pull out a column value from a range of data. Here is
the problem:
When I use this fn on the same page as the referenced range, it works fine.
For example,
=VLOOKUP(L2,A2:I18,5) looks up the value in L2, finds it in the first column
and returns the 5th column data... BUT

=VLOOKUP(G6,'Master Prep List'!A2:I18,5) where G6 represents a value found
in the same range on a different worksheet, here Master Prep List, returns
#N/A.

This does not seem correct to me, unless I am not using the fn correctly,
which is always a possibility. I am using Excel 2007,

Any help/insight would be appreciated.

If the second formula is on row 6, it is probably the result of a Copy and
Paste action. Are you sure that in this formula the range 'Master Prep
List'!A2:I18 is addressed and not 'Master Prep List'!A6:I22 ?
It is always safer to make the addresses of table absolue (like $A$2:$I$18)
or, even better, use a defined name.

Another possibility is that there are spaces in either the search argument
or the table. Or, if one of them was copied from a website or imported from
another program, other invisible characters.

Anyway, in your case, with the 4th argument omitted, #NA suggests that the
search argument is smaller than the first item from the table.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Pradhan" wrote in message
...
I am using VLOOKUP() to pull out a column value from a range of data. Here
is
the problem:
When I use this fn on the same page as the referenced range, it works
fine.
For example,
=VLOOKUP(L2,A2:I18,5) looks up the value in L2, finds it in the first
column
and returns the 5th column data... BUT

=VLOOKUP(G6,'Master Prep List'!A2:I18,5) where G6 represents a value found
in the same range on a different worksheet, here Master Prep List,
returns
#N/A.

This does not seem correct to me, unless I am not using the fn correctly,
which is always a possibility. I am using Excel 2007,

Any help/insight would be appreciated.

If I may add, another possibility is that the Master Prep List sheet has an
#N/A in one of the cells. If so, VLOOKUP will always return #N/A. Remove or
correct your #N/A from MAster Prep List, and it will work.

"Niek Otten" wrote:

If the second formula is on row 6, it is probably the result of a Copy and
Paste action. Are you sure that in this formula the range 'Master Prep
List'!A2:I18 is addressed and not 'Master Prep List'!A6:I22 ?
It is always safer to make the addresses of table absolue (like $A$2:$I$18)
or, even better, use a defined name.

Another possibility is that there are spaces in either the search argument
or the table. Or, if one of them was copied from a website or imported from
another program, other invisible characters.

Anyway, in your case, with the 4th argument omitted, #NA suggests that the
search argument is smaller than the first item from the table.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Pradhan" wrote in message
...
I am using VLOOKUP() to pull out a column value from a range of data. Here
is
the problem:
When I use this fn on the same page as the referenced range, it works
fine.
For example,
=VLOOKUP(L2,A2:I18,5) looks up the value in L2, finds it in the first
column
and returns the 5th column data... BUT

=VLOOKUP(G6,'Master Prep List'!A2:I18,5) where G6 represents a value found
in the same range on a different worksheet, here Master Prep List,
returns
#N/A.

This does not seem correct to me, unless I am not using the fn correctly,
which is always a possibility. I am using Excel 2007,

Any help/insight would be appreciated.

Thank you all for your input. I am quite certain the formulas are correct.I
believe the problem has to do with the fact the 2nd worksheet on which I am
using (requiring the referenced range in VLOOKUP) has a pivot table on it.
There is something about this in the help menu.

Even though I am trying to enter the formula in a field far removed from the
Pivot table, I get this error anywhere on that worksheet. Is there some way
to limit what I might call "the effectual" range of the Pivot tabe?

Thanks again,
Pradhan

"Sean Timmons" wrote:

If I may add, another possibility is that the Master Prep List sheet has an
#N/A in one of the cells. If so, VLOOKUP will always return #N/A. Remove or
correct your #N/A from MAster Prep List, and it will work.

"Niek Otten" wrote:

If the second formula is on row 6, it is probably the result of a Copy and
Paste action. Are you sure that in this formula the range 'Master Prep
List'!A2:I18 is addressed and not 'Master Prep List'!A6:I22 ?
It is always safer to make the addresses of table absolue (like $A$2:$I$18)
or, even better, use a defined name.

Another possibility is that there are spaces in either the search argument
or the table. Or, if one of them was copied from a website or imported from
another program, other invisible characters.

Anyway, in your case, with the 4th argument omitted, #NA suggests that the
search argument is smaller than the first item from the table.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Pradhan" wrote in message
...
I am using VLOOKUP() to pull out a column value from a range of data. Here
is
the problem:
When I use this fn on the same page as the referenced range, it works
fine.
For example,
=VLOOKUP(L2,A2:I18,5) looks up the value in L2, finds it in the first
column
and returns the 5th column data... BUT

=VLOOKUP(G6,'Master Prep List'!A2:I18,5) where G6 represents a value found
in the same range on a different worksheet, here Master Prep List,
returns
#N/A.

This does not seem correct to me, unless I am not using the fn correctly,
which is always a possibility. I am using Excel 2007,

Any help/insight would be appreciated.

Is the value in G6 in the first column of the range 'Master Prep
List'!A2:I18? If not, adjust the range so it is.


"Pradhan" wrote:

I am using VLOOKUP() to pull out a column value from a range of data. Here is
the problem:
When I use this fn on the same page as the referenced range, it works fine.
For example,
=VLOOKUP(L2,A2:I18,5) looks up the value in L2, finds it in the first column
and returns the 5th column data... BUT

=VLOOKUP(G6,'Master Prep List'!A2:I18,5) where G6 represents a value found
in the same range on a different worksheet, here Master Prep List, returns
#N/A.

This does not seem correct to me, unless I am not using the fn correctly,
which is always a possibility. I am using Excel 2007,

Any help/insight would be appreciated.

that shouldn't be it. I use VLOOKUP against Pivot Tables on a daily basis.

And you are absolutely sure the value in G6 does appear in column A of
Master Prep List? If you copy the cell and perform a Find, do you locate the
value?

"Pradhan" wrote:

Thank you all for your input. I am quite certain the formulas are correct.I
believe the problem has to do with the fact the 2nd worksheet on which I am
using (requiring the referenced range in VLOOKUP) has a pivot table on it.
There is something about this in the help menu.

Even though I am trying to enter the formula in a field far removed from the
Pivot table, I get this error anywhere on that worksheet. Is there some way
to limit what I might call "the effectual" range of the Pivot tabe?

Thanks again,
Pradhan

"Sean Timmons" wrote:

If I may add, another possibility is that the Master Prep List sheet has an
#N/A in one of the cells. If so, VLOOKUP will always return #N/A. Remove or
correct your #N/A from MAster Prep List, and it will work.

"Niek Otten" wrote:

If the second formula is on row 6, it is probably the result of a Copy and
Paste action. Are you sure that in this formula the range 'Master Prep
List'!A2:I18 is addressed and not 'Master Prep List'!A6:I22 ?
It is always safer to make the addresses of table absolue (like $A$2:$I$18)
or, even better, use a defined name.

Another possibility is that there are spaces in either the search argument
or the table. Or, if one of them was copied from a website or imported from
another program, other invisible characters.

Anyway, in your case, with the 4th argument omitted, #NA suggests that the
search argument is smaller than the first item from the table.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Pradhan" wrote in message
...
I am using VLOOKUP() to pull out a column value from a range of data. Here
is
the problem:
When I use this fn on the same page as the referenced range, it works
fine.
For example,
=VLOOKUP(L2,A2:I18,5) looks up the value in L2, finds it in the first
column
and returns the 5th column data... BUT

=VLOOKUP(G6,'Master Prep List'!A2:I18,5) where G6 represents a value found
in the same range on a different worksheet, here Master Prep List,
returns
#N/A.

This does not seem correct to me, unless I am not using the fn correctly,
which is always a possibility. I am using Excel 2007,

Any help/insight would be appreciated.