0

I have a list of numbers in a random set sequence from A2:A165000. In D1 I have my target number as a column heading: 2588. In this column I would like to generate a list all of the numbers that occur directly before my target. And in E1 a list of the numbers that occur directly after my target.

I am able to find a single instance with the following formulae:

=INDEX(A2:A165000,MATCH(2588,A2:A165000,0)-1)

=INDEX(A2:A165000,MATCH(2588,A2:A165000,0)+1)

How do I expand this to find all of the other instances?

  • Please see comments on answers – Roby Vicary Aug 1 at 14:58
1

Based on the comments from the OP below, I have made changes to the previous formulae. Use the following formulae on D2 and E2 respectively:

=IFERROR(INDEX($A$2:$A$25,SMALL(IF($A$2:$A$25=$D$1,ROW($A$2:$A$25)-2),ROW(1:1))),"")
=IFERROR(INDEX($A$2:$A$25,SMALL(IF($A$2:$A$25=$D$1,ROW($A$2:$A$25)),ROW(1:1))),"")

Then drag/autofill them on the cells below.

In this example, 2588 is listed 3 times. 3 numbers are returned:

  • On column D, the numbers that are 1 row before it
  • On column E, the numbers that are 1 row after it

IMG:

| improve this answer | |
  • Thanks, this formula is good in that I would like to know the numbers up to approximately 7 either side of the target number. But considering there are multiple occurrences of the target number I would like the initial to only list one number before, and the second formula to only list one number after. Any ideas? – Roby Vicary Aug 1 at 14:57
  • @RobyVicary If I understand, you would like one 1 number to be listed (1 before and one after), but to use only one formula to get the results. Is that correct? It seems to contradict what you ask on the question: "How do I expand this to find all of the other instances". Can you explain what you then mean by "other instances"? – Reddy Lutonadio Aug 1 at 15:01
  • I don't mind if one formula is used to find the number one row before and a different formula is used to find the numbers a row after, I would just put them in different columns - just like you have done. If you could combine the two that would be great, I will end up doing that anyway in order to create a tally of how many times each number occurs; but the more important thing for now is that it is able to correctly identify each occurrence of the target number and each co-occuring number. – Roby Vicary Aug 1 at 15:17
  • 1
    So if 2588 occurs for example 3 times, then only the numbers appearing 1 row before and after should be shown. Correct? – Reddy Lutonadio Aug 1 at 15:19
  • Yes, exactly, infact I know by another method that it occurs approximately 160 times. – Roby Vicary Aug 1 at 15:24

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.