Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Mutiple Criteria

    Data:
    A1: 1/5/16 B1: 1/6/16 C1: 1/7/16 D1: 1/7/16
    A2: 4000 B2: 3600 C2: 4000 D2: 3600
    A3: 415 B2: 432 C3: 418 D3: 435
    A4:
    A5: Date Entry

    Using the above data, I enter a date in cell A5. I have two tables that need to be populated based on A5. I am looking for value in row 3, the issue i am having is in 1/7/16 there are two entries for that day. Currently I am using this formula:
    Code:
    =IF(OFFSET(INDIRECT(CELL("address",INDEX(A1:AA1,MATCH(A5,A1:AA1,1)))),1,0,1,1)=3600,OFFSET(INDIRECT(CELL("address",INDEX(A1:AA1,MATCH(A5,A1:AA1,1)))),2,0,1,1),"")
    This works fine when there is only one entry a day. How can i make this formula look for that date and the strength(row 2), to determine which answer goes in the correct table?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Jackal,

    Consider using a much easier User Defined Function (UDF). Place the following code in a standard module

    Code:
    Public Function Num(rng As Range, strength As Long) As Long
    Application.Volatile
    Dim cell As Range
    For Each cell In rng
        If cell = [a5] And cell.Offset(1, 0) = strength Then
            Num = cell.Offset(2, 0)
        End If
    Next cell
    End Function
    In the cell where you have your formula, replace it with =Num(Range, strength) where range are the dates in row 1 and strength is the strength criteria you wish to use.

    Ex. =Num(A1:E1,3600)

    Will search for the date in A5 that matches the dates in A1:E1 with a strength of 3600 then returns the value in the third row of the same column (435).

    strength1.png

    Your choice:

    =IF(OFFSET(INDIRECT(CELL("address",INDEX(A1:AA1,MA TCH(A5,A1:AA1,1)))),1,0,1,1)=3600,OFFSET(INDIRECT( CELL("address",INDEX(A1:AA1,MATCH(A5,A1:AA1,1)))), 2,0,1,1),"")

    OR

    =Num(A1:E1,3600)

    HTH,
    Maud
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts
    your suggestion is working wonderfully...thank you.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You might also use:
    =IFERROR(LOOKUP(2,1/($A$1:$AA$1=A5)/($A$2:$AA$2=3600),A3:AA3),"")
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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