VLOOKUP or IF with many criteria

[Guest]

Suppose that I have a sheet with a list of staff as follows:

A B C D
1 Name Age Work.Yrs Staff Level
2 Mr.A 30 3 ?
3 Ms.B 45 5 ?
4 Ms.C 22 1 ?

And another table below this list:

6 Age Work.Yrs Staff Level
7 <30 <2 A
8 >=30 >=2 B

How to make cells in column D automatically refer to the reference table? I
have tried a lot of times with VLOOKUP but failed, and I think this function
is not suitable to range criteria (<,>). If there are a lot of criteria here,
i.e. 10, function IF also fails.

How do I deal with this situation? Is there anybody who can help me?

Thanks alot.

 

[T. Valko]

You only have 2 conditions which could be handled with an IF function: age
30 and work yrs 2

However, your criteria has some "holes" in it.

6 Age Work.Yrs Staff Level
7 <30 <2 A
8 >=30 >=2 B

What if the age is >=30 and work yrs is <2?

What if the age is <30 and work yrs is >=2?

Biff

 

[carlos]

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 D24 using the
formula :

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

Regards