View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] carlos@cea.ucp.pt is offline
external usenet poster
 
Posts: 4
Default VLOOKUP or IF with many criteria

Its not easy to make in plain excel...

But is very easy to create a UDF to do the job...

Function GetStaff(CritAge As Range, age, CritWork As Range, work,
StaffRange)
Dim CritRows As Integer, found As Boolean
Dim b1 As Boolean, b2 As Boolean
Dim r As Integer
CritRows = CritAge.Rows.Count
r = 1
Do While r <= CritRows And Not found
b1 = Evaluate(age & CritAge.Cells(r, 1))
b2 = Evaluate(work & CritWork.Cells(r, 1))
found = b1 And b2
r = r + 1
Loop
If found Then
GetStaff = StaffRange.Cells(r - 1, 1)
Else
GetStaff = 0
End If

End Function
----------------------------
Using your example you can call the function in D2:D4 using the
formula :

=getStaff($A$7:$A$8;B2;$B$7:$B$8;C2;$C$7:$C$8)

Regards