LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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



 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup with two criteria hollister22nh Excel Worksheet Functions 1 February 25th 06 02:42 PM
vlookup with two criteria hollister22nh Excel Worksheet Functions 2 February 25th 06 11:42 AM
VLOOKUP with MAX criteria tralls Excel Discussion (Misc queries) 2 October 26th 05 12:12 AM
vlookup with two criteria BJ Excel Worksheet Functions 2 March 29th 05 10:37 PM
two criteria in a vlookup CMAC Excel Worksheet Functions 5 February 4th 05 05:49 PM


All times are GMT +1. The time now is 05:20 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"