You may find useful if you are looking for a data located in the next and/or previous cell based on a specific word. For instance, you have a huge data and you want to know what is before and after a cell that contains “chocolate”.Also, I will tell you how to search the data above and/or below.

When I use the formula ?

To know the data before and after the cell that contains the particular word I am looking for.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How are the formulas ?

=INDEX()

=MATCH()

This formula is to search in a row:

• Previous cell in E2: =INDEX(A2:C2,MATCH("chocolate",A2:C2,0)-1)
• Next cell in F2: =INDEX(A2:C2,MATCH("chocolate",A2:C2,0)+1)

“Chocolate” is in a cell in the row 2 between A and C, if it finds “chocolate”, it will show you the previous and next data in the same row.

This formula gets the same result but it searches in a column showing the data in a row:

• Previous column in E3: =INDEX(A:C,MATCH("chocolate",B:B,0)+0,1)
• Next column inF3: =INDEX(A:C,MATCH("chocolate",B:B,0)+0,3)

“Chocolate” is in a cell in the column B, if it finds “chocolate”, it will show you the previous (column A=1) and next (column C=3) data.

This formula is to search in a column showing the data in a column:

• Above in G2: =INDEX(A:C,MATCH("chocolate",B:B,0)-1,2)
• Below in H2: =INDEX(A:C,MATCH("chocolate",B:B,0)+1,2)

“Chocolate” is in a cell in the column B=2, if it finds “chocolate”, it will show you the data of the above and below rows.

If you don’t know the full name, you can use the wildcard alias asterisk/star:

=INDEX(A:C,MATCH("*oco*",B:B,0)+0,3)

In the other hand, if you are using a cell reference with wildcard, you have to use this one:

=INDEX(A:C,MATCH("*"&B1&"*",B:B,0)+0,3)