returning text; alternative to sumproduct/sumif?

[ker_01]

I need to check a range and identify each situation where two cells match my
criteria, and in those situations, return the text value of one of those
cells.

My attempts so far which include sumproduct, match, sumif, etc don't seem to
work; sumproduct and sumif seem to want numeric values, match isn't working
because I have to use a wildcard character and the first match is not always
the right one, and I haven't found anything else that will evaluate multiple
cells and return a text value only when both cells meet my criteria.

Are there any other formulas I should play with? I'm reluctant to use array
formulas because this formula will include sumproduct and other formulas, and
my initial testing wasn't fruitful, and I was thinking it might have to do
with entering things like a sumproduct Within an array formula.

=Count(And(criteria1, criteria2))
does provide the expected result (1 match); I just can't figure out how to
tell which column it is in, and return the text from one of my target cells.

I welcome any advice-

Thank you,
Keith

 

[Jacob Skaria]

SUMPRODUCT() actually picks the total. If you only have a uniqe record
matching the 2 criterias it works good or otherwise will return the total of
the duplicate records matching both criterias..............Array formula
solution

=INDEX(col3,MATCH(1,(col1=criteria1)*(col2=criteria2),0))

If this post helps click Yes

 

[T. Valko]

See if this is what you had in mind...

...........A..........B..........C
1........x...........z..........AAA
2........z...........z..........BBB
3........x...........y.........CCC
4........y...........x.........DDD

You want to lookup x in col A and y in col B and return the corresponding
value from col C.

Array entered** :

=INDEX(C1:C4,MATCH(1,(A1:A4="x")*(B1:B4="y"),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Ashish Mathur]

Hi,

```````````-
jjhh77

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Hi,

Try this

=INDEX(A5:C8,MATCH(1,index((A5:A8="x")*(B5:B8="y"),,),0),3)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com