=ArrayFormula(IF(ISBLANK(Input!$B4:$B)," ",(INDEX(Input!A4:A, SMALL(IF(COUNTIF($A$3,Input!$B4:$B), MATCH(ROW(Input!$B4:$B), ROW(Input!$B4:$B)), ""), ROWS(A6)), COLUMNS(Input!B4:B)))))

The result is the first record repeated 3 time:

enter image description here

  • Hello and welcome, Can you edit and ask us a question ? – pjmg Oct 17 '19 at 9:32
  • The result is the first record that met the criteria. And that result is repeated 3 times. And there are 3 records that actually meets the criteria. Its should display 3 different records that meets the criteria. – palani nadarajan naidu Oct 17 '19 at 9:49
  • 1
    Can you please edit your question to add these elements for other people ? Also in which cell is your fomula ? wich cells is your criteria(s) ? etc.. be sure to vist How to ask – pjmg Oct 17 '19 at 10:17
  • The formula is at row 6, Starting from A6 to G6... The Criteria is at A3... – palani nadarajan naidu Oct 17 '19 at 13:56
  • Instead of Index() + Match() you can try a Query(). If you provide us a dummy sheet with some sample data we can try to help you. For example, you have a countif with Input!$B4:$B as your condition : what kind of data it contains ? – pjmg Oct 18 '19 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.