Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Can this be done?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    CA
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think it can be but I am struggling.

    In the example below, I want to use the $400 in the 'formula' spots. I know the first column doesn't match but need to lookup the $400 based on the 2nd and 3rd columns matching.

    dog five car $400
    cat five bus 'formula'
    pig five car 'formula'
    bird five car 'formula'

    thanks

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you explain further please I don't fully understand what you mean, especially when you say 2nd and 3rd column matching???

    RET79

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    CA
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In the example, the 3rd and 4th rows both have 'five' in the 2nd column and 'car' in the 3rd.

    I need these rows to essentially lookup rows with the same specs (five, car) and pull that $400.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-01 17:04, geolefty wrote:
    In the example, the 3rd and 4th rows both have 'five' in the 2nd column and 'car' in the 3rd.

    I need these rows to essentially lookup rows with the same specs (five, car) and pull that $400.
    In E2 enter: five
    In F2 enter: car

    =SUMPRODUCT((B2:B20=E2)*(C2:C20=F2),D2:D20)

    Or use DSUM, which requires the appropriate labels from your data in E1 and F1 in addition to conditions in E2 and F2:

    =DSUM(Database,4,E1:F2)

    where Database refers to your data range including labels/column headings.

    You could also consider using Pivot Tables.


  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    CA
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin I think we could be on the right track although I think I might be trying to do something almost impossible.

    Although if anyone can figure it out it is you and others here.

    I tried to use the sumproduct in the same column as the number I am trying to lookup and I get circular refs.

    Essentially some of the rows have pricing I want to use (like the $400) when related to 'dog'. The other rows I need to pull that pricing when the other columns match (five, car) but do not match 'dog'.

    i am trying in a seperate column, but that can be a pain since i have about 12 diff column to do this to.

    any other ideas?

    thanks
    thank again

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-01 16:48, geolefty wrote:
    I think it can be but I am struggling.

    In the example below, I want to use the $400 in the 'formula' spots. I know the first column doesn't match but need to lookup the $400 based on the 2nd and 3rd columns matching.

    dog five car $400
    cat five bus 'formula'
    pig five car 'formula'
    bird five car 'formula'

    thanks
    If I understand you correctly, if the entry in the second column of rows 2,3,and 4th column is 'five', then you would want the corresponding entry in the 4th column
    to be the same as the entry in the 4th column of row 1 if it is so, then use
    the following formula in the 4th column of rows 2,3, and 4 --

    =VLOOKUP("five",{"five","car",400},3)

    If I have not understood you correctly, or it does not work for you, please explain a little further ... and let us take it from there!

    Regards

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    CA
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need the $400 in other rows if column 2 AND 3 match.
    One additional problem is that the $400 is not constant.

    Let me try to explain a little further with more specific data.
    I have regional data (5 regions) and I have north america pricing. For any of the other regions (not north america) I want to take the NA pricing and factor up or down from it. I need to take the NA pricing for similar data. I need to take the NA price for a 12-inch TV and use that to factor the price for a Europe 12-inch TV. and NA 14-inch TV to get the Europe 14-inch price.

    I have many products and lots of pricing so I wanted something pretty dynamic and I just dont know if I can get it.

    My thought is to check and see if a cell is north america, if so then use than pricing. If not then check other characteristics and make sure they are similar to use for the comparison.

    It sounds simple to me but I am just stuck.

    thanks

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I believe your data is too extensive to explain in a few words on the board. You should either post some real data on the board so the viewers can bite into the real thing. If you care to email your file to me, I will be glad to look at it and help if i can.

    Regards!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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