| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup?
I'm trying to convert text to numbers and failing, can you help?
I've tried this: =LOOKUP(N2,{"N","3c","3b","3a","4c","4b","4a","5c" ,"5b","5a","6c","6b","6a","7c","7b","7a"},{1,2,3,4 ,5,6,7,8,9,10,11,12,13,14,15,16}) I want the text N in a cell to return the number 1, 3c to return 2, 5c to return 8, 7a to return 16 etc. I've stared at it for ages and now I'm stuck, it's not working. Can anyone help? Many thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup?
Try
=Match(N2,{"N","3c","3b","3a","4c","4b","4a","5c", "5b","5a","6c","6b","6a","7c","7b","7a"},0) Hope this help. "pearce" wrote in message ... I'm trying to convert text to numbers and failing, can you help? I've tried this: =LOOKUP(N2,{"N","3c","3b","3a","4c","4b","4a","5c" ,"5b","5a","6c","6b","6a","7c","7b","7a"},{1,2,3,4 ,5,6,7,8,9,10,11,12,13,14,15,16}) I want the text N in a cell to return the number 1, 3c to return 2, 5c to return 8, 7a to return 16 etc. I've stared at it for ages and now I'm stuck, it's not working. Can anyone help? Many thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup?
Lookup parameters must be in ASCENDING order: 3a,3b,3c .... N etc "pearce" wrote: I'm trying to convert text to numbers and failing, can you help? I've tried this: =LOOKUP(N2,{"N","3c","3b","3a","4c","4b","4a","5c" ,"5b","5a","6c","6b","6a","7c","7b","7a"},{1,2,3,4 ,5,6,7,8,9,10,11,12,13,14,15,16}) I want the text N in a cell to return the number 1, 3c to return 2, 5c to return 8, 7a to return 16 etc. I've stared at it for ages and now I'm stuck, it's not working. Can anyone help? Many thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup?
The lookup values, N-7c need to be in ascending sort order, so 3a should be
first and N will be last. "pearce" wrote: I'm trying to convert text to numbers and failing, can you help? I've tried this: =LOOKUP(N2,{"N","3c","3b","3a","4c","4b","4a","5c" ,"5b","5a","6c","6b","6a","7c","7b","7a"},{1,2,3,4 ,5,6,7,8,9,10,11,12,13,14,15,16}) I want the text N in a cell to return the number 1, 3c to return 2, 5c to return 8, 7a to return 16 etc. I've stared at it for ages and now I'm stuck, it's not working. Can anyone help? Many thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup?
Brilliant, thank you!
"Santipong" wrote: Try =Match(N2,{"N","3c","3b","3a","4c","4b","4a","5c", "5b","5a","6c","6b","6a","7c","7b","7a"},0) Hope this help. "pearce" wrote in message ... I'm trying to convert text to numbers and failing, can you help? I've tried this: =LOOKUP(N2,{"N","3c","3b","3a","4c","4b","4a","5c" ,"5b","5a","6c","6b","6a","7c","7b","7a"},{1,2,3,4 ,5,6,7,8,9,10,11,12,13,14,15,16}) I want the text N in a cell to return the number 1, 3c to return 2, 5c to return 8, 7a to return 16 etc. I've stared at it for ages and now I'm stuck, it's not working. Can anyone help? Many thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
| Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
| How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
Linear Mode
