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:



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

  • Please see comments on answers Aug 1, 2020 at 14:58

1 Answer 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:


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


  • 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? Aug 1, 2020 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"? Aug 1, 2020 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. Aug 1, 2020 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? Aug 1, 2020 at 15:19
  • Yes, exactly, infact I know by another method that it occurs approximately 160 times. Aug 1, 2020 at 15:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .