returning text; alternative to sumproduct/sumif?

  • Thread starter Thread starter ker_01
  • Start date Start date
K

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
 
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
 
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.
 
Back
Top