Trouble with Simple Array Formula

Edit: Used a vlookup instead of an array, and it seems to be working fine. Sorry for any trouble.


Hi. Well, this seemed like a simple formula to me, but for some reason I just can't get it to work.

If you look at the attached file, I am trying to get the formula in cell A8 on the first workbook to work. All I want to do is find the listed state in cell A1 on the first workbook, entitled "Calc", on the second workbook entitled "rates" in column A, and return the percentage in column B.

One thing I noticed was that the LA in cell A1 is actually 'LA, so I tried changing the LA on the "Rates" workbook in cell A27 to the same thing, but that didn't work. I also tried changing the 'LA on the "Calc" workbook to LA so that it matched cell A27 on the "Rates" workbook, but that didn't work either.

Thanks for looking.

A few points:
1) An "array formula", in Excel, is a special kind of formula that is completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER).
2) In SUMIF and SUMIFS, the arguments are reversed.
SUMIF(Criteria_range,Criteria,Value_range)
SUMIFS(Value_range,Criteria_range,Criteria,Criteria_range,Criteria,etc)

3) Using SUMIF on a large list when you only need one value can be an inefficient use of Excel's processing resources.
4) Referencing an entire column when you don't need to can also be an inefficient use of Excel's processing resources.
5) Try this regular formula in your posted workbook

Please Login or Register  to view this content.

Does that help?