At my wit's end! Use DGET or VLOOKUP to select data????
 

OK...I've tried everything I know of. I have two worksheets in a
spreadsheet. On worksheet X, there are two columns and 1100 rows. Column A
is a list of store numbers sorted in ascending order, but they are not
sequential. Column B is the corresponding name of the store. On worksheet
Y, I want to type in the number of a store in a long list of rows under
column A and return the name of the store in the same row in column B.

I can get the VLOOKUP formula to work, but with this problem: if I type in a
store number on worksheet Y that does not appear at all on worksheet X, it
will return a store name anyway. I want to make sure that a store number
entered on worksheet Y is valid, otherwise, return an error.

DGET works perfectly to detect an error, but it will not work in a row by
row format; I have to have a column heading and a single cell below for the
criteria (store number)
--
Allen

=IF(ISNA(VLOOKUP(A1,SheetX!A1:B1100,2,False)),"Err or",VLOOKUP(A1,SheetX!A1:B
1100,2,False))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Allen" wrote in message
...
OK...I've tried everything I know of. I have two worksheets in a
spreadsheet. On worksheet X, there are two columns and 1100 rows. Column
A
is a list of store numbers sorted in ascending order, but they are not
sequential. Column B is the corresponding name of the store. On
worksheet
Y, I want to type in the number of a store in a long list of rows under
column A and return the name of the store in the same row in column B.

I can get the VLOOKUP formula to work, but with this problem: if I type in
a
store number on worksheet Y that does not appear at all on worksheet X, it
will return a store name anyway. I want to make sure that a store number
entered on worksheet Y is valid, otherwise, return an error.

DGET works perfectly to detect an error, but it will not work in a row by
row format; I have to have a column heading and a single cell below for
the
criteria (store number)
--
Allen