Look up non numeric value (upwards)

**gombi** · 07-30-2012, 10:02 AM

Hi,

I was wondering if someone could point me in the right direction please…

I have a list (see below) and need to search for entries. There are names and numbers. User will enter a number and I need to show the next name searching upwards.
The list is very long and we have all in column A.

For example… a search for 11111111 should return Fred.

Bob
32123123
21321312
56456566
Fred
54656566
11111111
34345455
32434344
33434344
Marry
56677689

Thanks a lot!

**gombi** · 07-30-2012, 07:10 PM

Does someone have an idea on how I can do that? I cant find a solution.

**olasa** · 07-30-2012, 07:50 PM

Try
=INDEX($A$2:$A$13;MATCH(MATCH(C2;$A$1:$A$13;0);IF(ISTEXT($A$2:$A$13);ROW($A$2:$A$13))))
Confirm the formula with Ctrl+Shift+Enter, not just Enter. Use ; or , as separators.
//Ola

**Marcol** · 07-30-2012, 08:35 PM

@ olasa
Using your example might this be a non-array alternative?

Formula:

Please Login or Register  to view this content.

Or with semicolon delimiters ...

Formula:

Please Login or Register  to view this content.

**gombi** · 07-31-2012, 09:40 AM

Thanks.

I tried this:

=INDEX(A2:INDEX(A:A;MATCH(C2;A:A;0));MATCH(REPT("z";255);A2:INDEX(A:A;MATCH(C2;A:A;0));1))

I only get a #N/A.

---------- Post added at 09:40 AM ---------- Previous post was at 08:38 AM ----------

Sorry...it works now. Thanks a lot! :-)

**gombi** · 07-31-2012, 11:25 AM

Now I have to find another thing in a different list (see list below). All will be in column A.
For example a search with Bob, should return:
C_bla
B_foo
I_bar

The categories like Bob or Rob are starting with a character from A to Z. On the other hand , the list I need to look up starts with C,B or I followed by _.

Bob
C_bla
B_foo
I_bar
Rob
C_abc
C_bjg
I_dfg
B_asd

Thanks again fro the great help! :-)