Trying to find * and ~ escape character not working

[DaveLembke]

=LOOKUP(Schedule!C5,{"2200","2200+2OT","2200+4OT","0200+4OT","0400+2OT","0600","0600+2OT","0600+4OT","1000+4OT","1200+2OT","0200","0200+2OT","0200+4OT","1800+4OT","2000+2OT","AL","~*","ML","LV","nomatch"},{"1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20"})

When * is found the value of 17 should be returned but instead I get #N/A

This is related to a project that I posted about in Computer Programming, BUT a seperate issue that wouldnt have fit the linearity of the initial information request so I posted this here as a seperate issue. The purpose of this function is to read in from the schedule sheet the start time and OT designation and code 1 thru 20 to add rules that would be later used in relation to the other post. Was not expecting the escape character to fail to work, so I figured I'd post here to see if anyone can point out what i am missing. On the Schedule sheet C5 is populated with *. I tested this bu changing * to X and changed ~* to X and it reported back with 17. Tried * by itself as "*" and it didnt like it, so I set it back to "~*" and figured I'd post here.

[TechnoGeek]

Not sure what you're trying to do here and I'm too lazy to look up the LOOKUP function right this second, but you may be able to use VLOOKUP instead. VLOOKUP takes a range of data in the worksheet and uses that as the lookup table. I'll check into the LOOKUP function and see if I can figure something out.

[DaveLembke]

The point of this is to assign a code such as 17 to a cell on sheet 3 if at sheet 1 in C5 there is an asterisk in that cell. Makes for making the rules easier with IF the value is 17 do this elseif 18 do this etc. Will make another part of this project easier by looking for numeric values vs string matching. So its like casting a string into an integer I guess, but the escape character ~ tilde is not working so it cant find * when using ~* as "~*"

Was thinking that LOOKUP function was going to be easy in that it would avoid having to use a lengthy IF statement chain to assign a value to a cell based on the targeted string info.

Was hunting around in google search and found this and thought of how much easier it is to assign a different value to a matched value so if you had A, B, C, D and 1, 2, 3, 4 and the cell on sheet 1 at C5 was populated with B the cell with this formula on sheet 3 would show a value of 2

=LOOKUP(Schedule!C5,{"2200","2200+2OT","2200+4OT","0200+4OT","0400+2OT","0600","0600+2OT","0600+4OT","1000+4OT","1200+2OT","0200","0200+2OT","0200+4OT","1800+4OT","2000+2OT","AL","~*","ML","LV","nomatch"},{"1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20"})

[TechnoGeek]

IMPORTANT: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value.

This may be part of your problem. Also, I've never heard of ~ being an escape character before, so I searched and found this article. The way I understand it, the ~ escapes those characters only in excel's filters and searches, but not, say, the LOOKUP function (which uses regular expressions).