Criteria based on multiple columns

**mjhopler** · 10-21-2009, 11:31 PM

I have a file in which I need to return a value based on a logic test of values in two columns. Can anyone please help me with this. While I am unable to upload the actual file I am working on for confidential reasons, but I have recreated a very simple file that if I could get a formula that will return the proper value in Column J of the attached based on Value 1 and Value 2, this would solve my problem. Hopefully this will be very easy for some of you experts, but over my head. I am on a tight deadline and some help would be greatly appreciated.

Thanks,

**teylyn** · 10-21-2009, 11:48 PM

Hi,

In J2 use

=SUMPRODUCT(--($A$2:$A$16=H2),--($B$2:$B$16=I2),$C$2:$C$16)

copy down.

**mjhopler** · 10-22-2009, 12:09 AM

This worked perfect for what I asked, but I guess I asked a bad question. The value I am trying to return is actually text, the formula provided is returning a zero. Can you take a look at the new attached file? This may be a more accurate question. Thanks

**mjhopler** · 10-22-2009, 12:10 AM

I didn't hit upload. Here is the file.

**teylyn** · 10-22-2009, 03:43 PM

OK, then. It often pays to describe the requirements completely up front, especially if text and/or numbers are involved.

For this scenario put into J2 and copy down

=INDEX($A$2:$C$16,MATCH(H2&I2,$A$2:$A$16&$B$2:$B$16,0),3)

Note that this is an array formula and must be confirmed with CTRL+SHIFT+ENTER, otherwise it won't work.

cheers