VLOOKUP or IF with many criteria
 

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.

VLOOKUP or IF with many criteria
 

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

"Alys" <Alys @discussions.microsoft.com wrote in message
...
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.

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