sumproduct with text
 

'============================================
'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..

sumproduct with text
 

Sorry Gary.. I'm not getting the expected result

Col A Col B Col C
123 | ABC | Manager
524 | SDF | Clerk
268 | FHE | Asst
458 | HTL | Clerk
123 | KIT | Clerk
467 | NIT | CSR
231 | GET | CSR
254 | BAT | CSR
268 | CAT | CSR

In the above table I have Ref# 123 (Col A) common for 'ABC' & 'KIT' (Col B)

In E2 I'll enter ref#, In F2 I'll enter Name and I should get designation in
G2

If in F2 I enter ABC, then I should get Manager in G2
If I enter KIT in F2, then I should get clerk in G2


Pls help..



"Gary Brown" wrote:

'============================================
'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..

sumproduct with text
 

=OFFSET(C1,MAX(ROW(1:9)*--(A2:A10=E2)*--(B2:B10=F2)),0)
' - Remember to use CTRL-SHIFT-ENTER to make the formula an array
' showing the '{' and "}" at the beginning and ending of the formula

------------
Hope This helps,
Sincerely,
Gary Brown



"Kashyap" wrote:

Sorry Gary.. I'm not getting the expected result

Col A Col B Col C
123 | ABC | Manager
524 | SDF | Clerk
268 | FHE | Asst
458 | HTL | Clerk
123 | KIT | Clerk
467 | NIT | CSR
231 | GET | CSR
254 | BAT | CSR
268 | CAT | CSR

In the above table I have Ref# 123 (Col A) common for 'ABC' & 'KIT' (Col B)

In E2 I'll enter ref#, In F2 I'll enter Name and I should get designation in
G2

If in F2 I enter ABC, then I should get Manager in G2
If I enter KIT in F2, then I should get clerk in G2


Pls help..



"Gary Brown" wrote:

'============================================
'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..