Web Development

Point value changed by question poster.

let's assume that your data consists of the series aaa/aab/aac/aad/aba.../aad: say 16 values and they are kept in column G. Thus cells G1 to G16 contain the data. Fill in cells H1 to H16 with the series 1 to 16.
Label A1 "Query": cell A2 will contain the string to be searched.
Label B1 "Row": type in this formula in B2 =VLOOKUP(A2,$G$1:$H$16,2,FALSE) this will give you the row number of the matched string.
Label C1 "Prev Cell": type in this formula in C2 =ADDRESS(B2-1,7,4) this will give you the cell address of the string PREVIOUS to your query string.
Label D1 "Next Cell": type in this formula in D2
=ADDRESS(B2+1,7,4) this will give you the cell address of the string NEXT to your query string.

Columns B, C and D are your working columns and may be hidden.

Label E1 "Previous Value" and type in this formula in E2 =IF(ISERROR(INDIRECT(C2)),"none",INDIRECT(C2)) This will give the value of the previous string or a "none" if there cannot be a prior cell (the topmost has been reached).

Label F1 "Next Value" and type in this formula in F2 =IF(INDIRECT(D2)=0,"none",INDIRECT(D2)) This will give the value of the next string or "none" if there no other cells (the bottom most has been reached)

Careful there is one bug: we are not checking if the query string exists: if it doesn't you will get a series of "N/A"s. You can trap this error but the formulae will become even longer.

This question was closed by the author