vlookup doesn't work consistently (I think), kindly help

[Pradhan]

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.

 

[Niek Otten]

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.

 

[Sean Timmons]

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.

 

[Pradhan]

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

 

[~L]

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.

 

[Sean Timmons]

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?