1. ## 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"})  Reply With Quote

2. Shot in the dark. Try =J7 as opposed to "=J7"  Reply With Quote

3. 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 ):  Reply With Quote

4. ## 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.  Reply With Quote

5. 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".  Reply With Quote

6. Thanks Midknyte. The formula you suggested produces a "0" no matter what is in the E column or the J column but I'll take a look at the IF/OR idea to see if that might be a solution.  Reply With Quote

7. Seems to work fine for me. I opened a new worksheet. I set J7 to 200. I pasted my formula into K7.

If I put Coronado in E7, then K7 = 200. If I put Akron in E7, K7 =0.

Do you need E5 or E7? In your first post it says E5, but the newer post says E7. You're going to have to play with it since we don't have a copy of your worksheet. Basically, you need to make an entry for each city in the OR section.  Reply With Quote

excel formula, lookup formula, spreadsheet formula 