Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Allen
 
Posts: n/a
Default 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
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

=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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
make a vlookup using a variable path Alex St-Pierre Excel Worksheet Functions 1 March 3rd 05 12:54 AM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 01:09 PM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 11:43 AM
Dynamic Formulas with Dynamic Ranges Ralph Howarth Excel Worksheet Functions 5 January 21st 05 09:44 AM
VLOOKUP not working Scott Excel Worksheet Functions 3 November 12th 04 09:06 PM


All times are GMT +1. The time now is 10:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"