Please post reply as email address is bogus to prevent spam.

I am using Excel Office 2003.

I am bringing in data from a weekly download which I put into a pivot
table for others to see. This is sales data by store. Pivot table
works great but not all stores have sales every week.

UPC - come out in columns C,D,E and F

with TL in G
Store ID
Week end Item

To the right of the pivot table I have a list of all store numbers
(columnI) and through a CountIF (column H) decide if they are on the
list or not. A conditional format highlights them in red if their
CountIf value is 0. To the right of each store number I have the four
UPC's I want to see if they sold (by store) or not. Columns J,K,L, and
M.

The store numbers from the Pivot table show up in column A5:500.

I want my formula in cell J5 to search A5:A500 for "5 Total" and
return me the value for C5, then my formula in cell K5 to search
A5:A500 for "5 Total" and return me the value for D5,
my formula in cell L5 to search A5:A500 for "5 Total" and return me
the value for E5,
my formula in cell M5 to search A5:A500 for "5 Total" and return me
the value for F5, etc. so it is dynamic with each weekly update.

In J5 I wrote =IF(a5:a500="5 total",c5,"Concern")

I get back #VALUE!

I hope I have made sense.

Thanks in advance for your help,

baz