0

I am trying to extract data from a worksheet using an array formula but it will only return one (the first it finds) value. The formula I am using is:

=INDEX($C$2:$C$8, SMALL(IF($B12=$B$2:$B$8, ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1, ""), 1)) found here

When I follow the example, I don't get the same result as the person that posted the answer. Could the formula have been "fat fingered"?

Community
  • 1
Frank Steinert
  • 1
  • 1
  • 1
  • 1
    @Dave it's an idiom(?) for accountants that mistype numbers on a calculator, back when tapes were still used and "10-key" was a skill. – Raystafarian Apr 19 '16 at 13:37

1 Answers1

0

I use the following formula to return multiple values matching the same criteria.

{=IFERROR(INDEX([Return Value Range],SMALL(IF([Criteria Range]=[Criteria],ROW([Criteria Range])),ROW(1:1))),"")}

When you enter it, don't include the {} and remember that Array Formulas have to be entered by hitting Ctrl + Shift + Enter instead of just Enter. If you hit just Enter, it will only return the first result.

Fercstar
  • 141
  • 4
  • This is not working for me with the sample data. – Frank Steinert Apr 19 '16 at 14:43
  • Are you displaying the results vertically or horizontally? If you are displaying them horizontally, you will need to manually increase ROW(1:1). The second column should have ROW(2:2) and the third ROW(3:3) etc. – Fercstar Apr 19 '16 at 14:50
  • Yes, I am displaying them horizontally. Is there a better way to increase it instead of manually? Looks like when I change one row and then the next, they all change. Thank you for your help! – Frank Steinert Apr 19 '16 at 15:20
  • There doesn't appear to be a way to have ROW(1:1:) automatically increase if the results are horizontal since the row doesn't change when you drag the formula horizontally. If the data can be displayed vertically, it will automatically increase the ROW() when you drag the formula down. – Fercstar Apr 19 '16 at 15:44