Results 1 to 7 of 7

Thread: Excel formula question

  1. #1
    Member
    Join Date
    Aug 2004
    Posts
    180

    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"})

  2. #2
    Senior Member michaeln's Avatar
    Join Date
    Jan 2002
    Location
    Ireland
    Posts
    619
    Shot in the dark. Try =J7 as opposed to "=J7"

  3. #3
    Member
    Join Date
    Aug 2004
    Posts
    180
    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 ):

  4. #4
    Member
    Join Date
    Aug 2004
    Posts
    180

    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.

  5. #5
    Stark Raving MOD Midknyte's Avatar
    Join Date
    May 2002
    Location
    Arkham Asylum
    Posts
    22,270
    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".

    =IF(OR(E7="Coronado",E7="San Diego"),J7,0)

  6. #6
    Member
    Join Date
    Aug 2004
    Posts
    180
    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.

  7. #7
    Stark Raving MOD Midknyte's Avatar
    Join Date
    May 2002
    Location
    Arkham Asylum
    Posts
    22,270
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •