Array Formulas Giving incorrect value

[ChemistB]

Was working in this thread

http://www.excelforum.com/excel-gene...-criteria.html

and trying to get result without dummy columns. All of my array formulas give a result of 3 when the result should be 5. See attached. Thanks!

[Marcol]

Is it not the case that VLOOKUP will only return the first found match if there are duplicates?
That might explain the apparrent discrepancy.

[daddylonglegs]

That's right, VLOOKUP doesn't accept a range as the first argument (or at least it doesn't work the way you might expect it to).....but LOOKUP does so given that the first column of location is sorted ascending you could use this to give 5

=SUMPRODUCT(--(LOOKUP(E2:E21,Location)=A2:A21))

There are still possible issues though because LOOKUP can give you a "closest match" so if I changed E7 to "Maine" the formula still gives 5.....

[daddylonglegs]

This version would weed out any "false positives" by ensuring that E2:E21 actually appears in the lookup range

=SUMPRODUCT((LOOKUP(E2:E21,Location)=A2:A21)*(COUNTIF(INDEX(Location,0,1),E2:E21)>0))

....one remaining issue could be that any entry in E2:E21 alphabetically before the first value in the lookup range will cause an error (#N/A), e.g. if E2 contains "aardvark" that formula gives an error....so you can avoid that by making it into an array formula, i.e.

=SUM(IF(ISNA(LOOKUP(E2:E21,Location)),"",IF((LOOKUP(E2:E21,Location)=A2:A21)*(COUNTIF(INDEX(Location,0,1),E2:E21)),1)))

confirmed with CTRL+SHIFT+ENTER

[brokenbiscuits]

The discrepancy is that the vlookup is only looking in E2, not E2-E21. So it finds 3 instances of Utah in A:A so returns 3.

I have no idea what the correct formula is though, but thought that input might help?!

[ChemistB]

Thanks DDL ! I will relay to original OP.

[daddylonglegs]

Actually I could make that last one a little simpler, no need for ISNA you can just use the COUNTIF as the test, i.e.

=SUM(IF(COUNTIF(INDEX(Location,0,1),E2:E21),IF(LOOKUP(E2:E21,Location)=A2:A21,1)))

confirmed with CTRL+SHIFT+ENTER

That still relies on the lookup range being sorted ascending