Multiple VLOOKUP's checking multiple Cells

macropod · #4 05-05-2011, 08:24 PM

Hi OTPM,

Add the following function to a code module attached to your workbook:

Code:

Option Explicit
Function RiskTest(Rng1 As Range, Rng2 As Range, Rng3 As Range) As String
Dim i As Long, j As Long
RiskTest = "On Time"
With Rng1
  For i = .Column To .EntireRow.End(xlToRight).Column
    For j = 1 To .Row - 1
      If .Offset(j - .Row, 1 - .Column).Value = .Offset(0, i - .Column).Value Then
        If .Offset(j - .Row, Rng3.Column - .Column).Value < Rng2.Value Then
          RiskTest = "At Risk"
          Exit Function
        End If
      End If
    Next
  Next
End With
End Function

You can then use this function like a formula. It takes three parameters:
• the address of the first predecessor to test
• the address of the cell holding the completion date against which to test the predecessors; and
• the column holding the predecessor completion dates.
So, if you want to test the the set of predecessors starting in I11 against a completion date in F13 and the predecessor completion dates are in column F, you'd used the formula:
=RiskTest(I11,F$13,F:F)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
filling multiple cells whislt using Filter	VinceO	Excel	1	05-09-2011 06:38 AM
Multiple task lists and multiple calendars	kballing	Outlook	0	01-18-2011 10:23 AM
Moving formula range multiple cells when moving sum over one cell	FraserKitchell	Excel	4	02-26-2010 10:38 AM
Set up multiple accounts or...	cabinfever1888	Outlook	2	08-28-2009 07:31 AM
How to transfer multiple cells from excel to word by formulas	justziggy	Word	5	05-23-2009 02:33 AM