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.