Write a formula that can look up and match a specific number and return the results for that specifi

[Guest]

I am tring to write a formula that will look up a store number on a "data sheet" and return all of the information about that store. Here the story: I copy data about the stores off our Intranet and paste it into Excel on the "data sheet." I have another worksheet that will list each District Manager and all of his/her stores. I want to write a formula that will automatically look at the "data sheet" to see if that store is listed. If it is listed, I want it to return the results listed in the row for that store. If it is not listed on the "data sheet," I would like it to leave the cell blank

Thanks,
Michelle

 

[Frank Kabel]

Hi Michelle
VLOOKUP is what you're looking for. See
http://www.mvps.org/dmcritchie/excel/vlookup.htm
for more details.

In your case probably something like
=IF(ISERROR(VLOOKUP(A1,'data_sheet'!$A$1:$B$500,2,0)),"",VLOOKUP(A1,'da
ta_sheet'!$A$1:$B$500,2,0))

which will get the value from column B if it exist

--
Regards
Frank Kabel
Frankfurt, Germany

I am tring to write a formula that will look up a store number on a

"data sheet" and return all of the information about that store. Here
the story: I copy data about the stores off our Intranet and paste it
into Excel on the "data sheet." I have another worksheet that will
list each District Manager and all of his/her stores. I want to write
a formula that will automatically look at the "data sheet" to see if
that store is listed. If it is listed, I want it to return the results
listed in the row for that store. If it is not listed on the "data
sheet," I would like it to leave the cell blank.

 

[Randall Roberts]

Vlookup is ok for this but a much easier solution is to use index and
match

Randall

 

[Max]

One way:

In Sheet1 (your "datasheet"),
--------------------------------------
Assume the data is set-up as below,
with col headers in row1,
data in row2 downwards

Store...Field1..Field2..etc
store1..xxxx......yyyy.......etc
store2..zzzz......aaaa....etc
store3..bbbb......cccc...etc

In Sheet2
-------------
Assume the names of the stores,
ie store1, store2, etc are listed in col A, A2 downwards
and Field1, Field2, etc are the labels in row1, B1 across

Put in B2:

=IF(ISBLANK($A2),"",IF(ISNA(MATCH(TRIM($A2),Sheet1!$A:$A,0)),"",OFFSET(Sheet
1!$A$1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,COLUMN()-1)))

Copy B2 across as many cols as there are Fields in sheet1,
then copy down as many rows as there are stores listed in col A

--
Blank cells in col A (if any), or unmatched store names
will return blanks [ ""]

TRIM() is used to improve robustness in matching the names,
to remove any inadvertent leading, trailing or extra in-between-words spaces
in the store names listed in col A

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

I am tring to write a formula that will look up a store number on a "data

sheet" and return all of the information about that store. Here the story:
I copy data about the stores off our Intranet and paste it into Excel on the
"data sheet." I have another worksheet that will list each District Manager
and all of his/her stores. I want to write a formula that will
automatically look at the "data sheet" to see if that store is listed. If
it is listed, I want it to return the results listed in the row for that
store. If it is not listed on the "data sheet," I would like it to leave
the cell blank.

 

[Frank Kabel]

Hi
use the following formula
=IF(ISNA(VLOOKUP(A15,Data!$D$3:$J$20,7,0)),"",VLOOKUP(A15,Data!$D$3:$J$
20,7,0))

--
Regards
Frank Kabel
Frankfurt, Germany

Now it will pick up the information for the correct store, but if

that store number is not listed, it will return "#N/A." Is there a way
that it can show nothing (blank) or a line to indicate no data
associated with that store number. Also, the Average will not pick up
due to the "#N/A" listed for that store number.