vlookup case

users
#1

Thanks for very helpful reply. It does seem to be a big drawback with these functions. I'll certainly try it but ive found a workaround that im using at the mo: ive defined two strings in adjacent cells and using string functions to find the position of a character in one string then extract the corresponding character from the other. Much less elegant and it was quite tiresome to implement but ive at least got it to work, in the limited cases that im using it so far. If i extend it to transliterate strings of arbitrary length (for now the max length ive needed is 3 chars) then i guess i'll probably have to get into macros.
Thanks
G.

#2

I was doing some testing with single characters, but then saw it was three
characters. Originally, just used the code of the character, changed code to
ascii, but with 3 multiple each value to get a number that matched.
In both put the row number in next column.

Put the match field in C15 both the formulas work, both formulas give the
row.

abc
                                                                   1
                                                            97098099
                                                                   1

aBc
                                                                   2
                                                            97066099
                                                                   2

aBC
                                                                   3
                                                            97066067
                                                                   3

Abc
                                                                   4
                                                            65098099
                                                                   4

AbC
                                                                   5
                                                            65098067
                                                                   5

Abc
                                                                   6
                                                            65098099
                                                                   6

Ab
                                                                   7
                                                            65098000
                                                                   7

aCb
                                                                   8
                                                            97067098
                                                                   8

AcB
                                                                   9
                                                            65099066
                                                                   9

aBC
                                                                  10
                                                            97066067
                                                                  10

AAC
                                                                  11
                                                            65065067
                                                                  11

ss
                                                                  12
                                                           115115000
                                                                  12

aBC

                                                            97066067

                                                                   3
“=VLOOKUP($C$15,C1:D12,2,0)

                                                                   3
“{=INDEX(B1:B12,MATCH(1,EXACT($C$15,A1:A12),0))}

Formula in C

“=CODE(MID(A15,1,1))*1000000+CODE(MID(A15,2,1))*1000+CODE(MID(A15,3,1))

Date sent: Fri, 10 Mar 2017 09:46:22 +0000 (UTC)