Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to lookup data based on two separate criteria. I made a stab at it with the match and vlookup functions. With MATCH I can get the start of the lookup area but not the end.I sure there's a much more elegant way than how I've approached this, so any help would be appreciated.


    Thanks
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use the following formula in J5:

    =SUMPRODUCT(($A$2:$A$40=$I5)*($B$2:$B$40=$J$2)*$C$ 2:$C$40)

    and fill down. The formula for K5 would be

    =SUMPRODUCT(($A$2:$A$40=$I5)*($B$2:$B$40=$J$2)*$D$ 2:$D$40)

    If there is no match, the formula will return 0, so you should hide zeros.


  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks! I came up with this also: =VLOOKUP($I5,INDIRECT("a"&MATCH($J$2,$B$1:$B$40,0) &":d"&MATCH($J$2,$B$1:$B$40,1)),COLUMN($C:$C))

    But your way is more flexible and easier to work with.

Posting Permissions

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