Excel formula question

• 11-20-2010, 10:40 AM
Kathleane
Excel formula question
Excel 2003

I have a spreadsheet that I want the values in Column R to equal the values in Column J but only if there are certain values in Column E. Below is the formula that I concocted while works halfway. It inserts the letter/number "J7" in Column R if the values in Column E match the formula but I want it to insert the VALUE in Column J7, not just the word "J7". I've gone over and over it and it's probably something really simple that I'm missing, but I can't figure it out. Any ideas?

=LOOKUP(E5,{"Coronado","San Diego","Poway","Mira Mesa","Sabre Springs","La Mesa","Chula Vista","Carmel Valley","San Pedro","San Elijo","Del Mar","Santee","El Cajon","La Jolla","Spring Valley","Alpine","La Verne","Oceanside"},{"=J7","=J7","=J7","=J7","=J7" ,"=J7","=J7","=J7","=J7","=J7","=J7","=J7","=J7"," =J7","=J7","=J7","=J7","=J7"})
• 11-21-2010, 07:57 PM
michaeln
Shot in the dark. Try =J7 as opposed to "=J7"
• 12-02-2010, 06:32 PM
Kathleane
Thanks michaeln. Shots in the dark are appreciated at this point, but that one didn't work. Right now I have the formula give me an "X" instead of the value in J7 and that works fine. But when I try to replace the "X" with "J7" it doesn't work. Well it works but not correctly. It inserts J7 into the cell with the formula, but not the value of Column J Row 7 which is what I want. I'm so close but so far away ):
• 12-02-2010, 06:49 PM
Kathleane
Excel lookup formula
Excel 2003

I'm trying to construct a formula that will place the text that is in Column J Row 7 into Column R Row 7 but only if Column E Row 7 contains certain text. I believe that I need to use a LOOKUP formula because the text in E7 that I'm looking for is a list of cities.

I've gone over and over it and it's probably something really simple that I'm missing, but I can't figure it out. Any ideas would be much appreciated. Even a link to another site. I've tried Googling this and so far haven't found anything.

=LOOKUP(E7,{"Coronado","San Diego","Poway","Mira Mesa","Sabre Springs","La Mesa","Chula Vista","Carmel Valley","San Pedro","San Elijo","Del Mar","Santee","El Cajon","La Jolla","Spring Valley","Alpine","La Verne","Oceanside"},{"=J7","=J7","=J7","=J7","=J7" ,"=J7","=J7","=J7","=J7","=J7","=J7","=J7","=J7 "," =J7","=J7","=J7","=J7","=J7"})

The final result that I'm looking for is that I want a total of the dollar amounts in Column J but only if Column E contains certain city names. My thinking was that I'd create a Column R to collect these values and then total Column R to get my total.
• 12-02-2010, 09:20 PM
Midknyte
I think Lookup will only work with ranges or value sets, not single cells.

You could try an IF OR statement, but you'd have to put in each E7="city".