#1
|
|||
|
|||
LOOKUP - Complex lookup with 2 lookups in 1 cell
Hi,
I am trying to use LOOKUP, but I have 2 things to lookup within 1 cell and want to concatenate them: Example: A 1 U(9) 2 M(8) 3 C(7) My lookup table: A B C 1 U All users 2 M Management 3 C Consultants 4 7 Read Only 5 8 Full Access 6 9 Read & Write I would like to be able to extract the "U(9)" in cell A1 as "All Users - Read & Write" I would like to know if there is a way to do this without having my U(9) in 2 separate cells. Thanks, Stephanie |
#2
|
||||
|
||||
Hi , supposing sheet2!$a$1:$b$6 is your lookup table and there is only one digit between parenthesis,
perhaps =vlookup(left(a1,1),sheet2!$a$1:$b$6,2,0)&" -"&vlookup(mid(a1,3,1),sheet2!$a$1:$b$6,2,0)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
I couldn't get that to work. The first part works, but if I do the second VLOOKUP on its own, I get #N/A for some reason.
|
#4
|
|||
|
|||
If the contents inside the parentheses U(9) always is a digit then put a double minus in front of the MID function.
|
#5
|
|||
|
|||
ok... now to throw a wrench into it...
I just looked at all the cells I need to do this for and they aren't all the same... I added a few more examples. The numbers in brackets are consistent, (0-9) but the values before the first parenthesis is different and varying length. Example: A 1 U(9) 2 M(8) 3 C(7) 4 D3.6.0(7) 5 W++(8) 6 W++(7) 7 D0.1+++(9) My lookup table: A B C 1 U All users 2 M Management 3 C Consultants 4 D3.6.0 5 D0.1+++ 6 W++ 7 7 Read Only 8 8 Full Access 9 9 Read & Write Is this still doable??? I'm guessing to use an expression search, "look at all characters before the first open parenthesis"? Thanks, Stephanie |
#6
|
||||
|
||||
Quote:
something like =--RIGHT(LEFT(A1,LEN(A1)-1),1)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
Figured it out!!!
=VLOOKUP(LEFT(TRIM(A1),LEN(TRIM(A1))-3),Sheet2!$A$1:$B$9,2,0)&" - "&VLOOKUP(--RIGHT(LEFT(TRIM(A1),LEN(TRIM(A1))-1),1),Sheet2!$A$1:$B$9,2,0) THANK YOU FOR ALL YOUR HELP!!!!!!!!!!!!!!!!!!!!!!! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Lookup | Tony Singh | Excel | 3 | 03-06-2015 11:03 AM |
LOOKUP and IF FUNCTION | midgetmogalle | Excel | 6 | 12-06-2013 09:35 AM |
Lookup | angie.chang | Excel | 1 | 07-27-2012 09:45 PM |
Possible Lookup | Karen222 | Excel | 3 | 01-10-2012 05:41 AM |
LookUp | aztiguen24 | Excel | 5 | 05-24-2011 03:57 AM |