Sumproduct for two way lookup with text values

[elloelloHllo]

Hello, I have the following data table which I'm trying to look up to:


Code COY1 COY2

1 Keep Keep

2 Reject Keep

3 Reject Reject

4 Keep Reject


For example, I want to find the result if Code =1 and Company = Coy2


I have used the following formula but it only works if I change the data in the table to values instead of text (keep, reject)which I don't really want to do


=sumproduct((table*(code=1)*(company="COY2)))


table, code and company are named ranges


Any help would be greatly appreciated


Thanks

 

[NARAYANK991]

Hi ,


Try a two-way match :

[pre]

INDEX(Data_Range,MATCH(Code,Code_Column,0),MATCH(Company,Header_Row,0))

[/pre]
where :


Data_Range is say A2:C5


Code is the particular code you wish to match , say 1


Code_Column is the column in which your code values are present , in this case A2:A5


Company is the particular company you wish to match , in this case COY2


Header_Row is the row where your headers are , say A1:C1


Narayan

 

[elloelloHllo]

Fantastic, thank you.