# Thread: Mutiple Criteria

1. ## 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. 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

OR

=Num(A1:E1,3600)

HTH,
Maud

3. your suggestion is working wonderfully...thank you.

4. You might also use:
=IFERROR(LOOKUP(2,1/(\$A\$1:\$AA\$1=A5)/(\$A\$2:\$AA\$2=3600),A3:AA3),"")

#### Posting Permissions

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