Sorting Issue

[tom.mat.walker]

Hi guys, I'm having a problem with sorting and a lookup function in excel.....

I have a list like so:

I1
I2
I3
I4
I5
I6
I7
I8
I9
L1
L2
L3
L4
L5
L6
L7
L8
L9
L10
L11
L12
L13
L14
L15
O1
O2
O3
O4
O5
O6
O7
O8
O9
O10


And when I use the lookup function it returns the wrong value because the sorting shows ,for example, L11 as being in the wrong place and hence returns the wrong value. Is there anything I can do about this?

Regards

Tom

[Glenn Kennedy]

I don't follow your explanation. can you attach a sample excel sheet showing the problem, the lookup formula, etc?

[JohnTopley]

What "lookup" function are you using?

[Ron Coderre]

The current order of the list is not in ascending order. Understand that your list is text. Consequently, L11 comes directly after L1 when sorted.
I would either:
• edit the list so that all trailing numbers are 2-digits: L1 would be L01, etc
or
• Create a "helper column", using formulas that make the trailing numbers 2-digits

Example:
=LEFT(A1,1)&RIGHT("00"&MID(A1,2,2),2)

• Reference the helper column in the LOOKUP formula

does that help?

[JohnTopley]

With data in B and your list in A this will find exact match for example "O2"

=INDEX($B$2:$B$35,MATCH("O2",$A$2:$A$35,0))

Is this what you want?

[tom.mat.walker]

Thanks guys, the idea to use double digits in the index has sorted it

[Ron Coderre]

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)