Web Development

General discussion

Gravatar
0 Votes
Locked

lookup data in excel

By tang0312 ·
Hi,
I'm trying to craft a query in ms excel 2003 that will look return the values. What I want to accomplish is type in data and the output would be the one before it and the one after it.

For example, if i have the following info:
a1 = aaa
a2 = aba
a3 = aca
a4 = ada
...

I want to be able to type in an input box (or other) 'aca' and the output will be aba & ada.

I'm sure that this is possible and i have been working with Vlookup, but i just can't seem to get it to work.

Any help would be appreciated.

tk

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by tang0312 In reply to lookup data in excel

Point value changed by question poster.

gravatar
Collapse -

by aamontalto In reply to lookup data in excel

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.

gravatar
Collapse -

by aamontalto In reply to

1 sorry made a typing mistake: the last string in the example should be "add" NOT "aad". Let me type the whole series:

aaa/aab/aac/aad/aba/abb/abc/abd/aca/acb/acc/acd/ada/adb/adc/add

Of course you can use any value this is an example.

Note the usual VLOOKUP limitations: the string series must be sorted so make sure you perform a data sort: also no duplicates should exist.

gravatar
Collapse -

by tang0312 In reply to

Sweet!

Works like a charm!

Thank you very much.

Regards,

Tim.

gravatar
Collapse -

by tang0312 In reply to lookup data in excel

This question was closed by the author

Back to Web Development Forum

Start or search

Related Discussions

Related Forums