'============================================
'Forumla Example 1:
'Using actual values as the search criteria in EXAMPLE 1:
'{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17="John")*--(C12:C17="Nov")),0)}
' or
'Forumla Example 2:
'Using cell references as the search criteria in EXAMPLE 1:
'{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17=A6)*--(C12:C17=A7)),0)}
'
' - Remember to use CTRL-SHIFT-ENTER to make the formula an array
' showing the '{' and "}" at the beginning and ending of the formula
'
'Note: Just as VLOOKUP will give you the FIRST value it finds that meets it's
' criteria, this formul will give you the LAST value it finds that meets
' all of it's criteria.
'
'============================================
--
------------
Hope This helps,
Sincerely,
Gary Brown



"Kashyap" wrote:

Hi,

I have Ref# in col A, Names in Col B and Designation in col C.. Both Col B &
C are text

I tried below formula to lookup both Col A & B and get Col C, but the result
i'm getting is 0

I tried same formula with numbers which worked fine..

=SUMPRODUCT(--(N22=A2:A30),--(Q22=B2:B30),C2:C30)

Can anyone help me out with this pls..

Thanks..