Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-04-2016, 09:36 AM
sglandon sglandon is offline LOOKUP - Complex lookup with 2 lookups in 1 cell Windows 7 64bit LOOKUP - Complex lookup with 2 lookups in 1 cell Office 2010 32bit
Novice
LOOKUP - Complex lookup with 2 lookups in 1 cell
 
Join Date: May 2016
Posts: 4
sglandon is on a distinguished road
Cool 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
Reply With Quote
  #2  
Old 05-04-2016, 09:43 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline LOOKUP - Complex lookup with 2 lookups in 1 cell Windows 7 64bit LOOKUP - Complex lookup with 2 lookups in 1 cell Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,505
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

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
Reply With Quote
  #3  
Old 05-04-2016, 09:53 AM
sglandon sglandon is offline LOOKUP - Complex lookup with 2 lookups in 1 cell Windows 7 64bit LOOKUP - Complex lookup with 2 lookups in 1 cell Office 2010 32bit
Novice
LOOKUP - Complex lookup with 2 lookups in 1 cell
 
Join Date: May 2016
Posts: 4
sglandon is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 05-04-2016, 08:50 PM
xor xor is offline LOOKUP - Complex lookup with 2 lookups in 1 cell Windows 10 LOOKUP - Complex lookup with 2 lookups in 1 cell Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,078
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

If the contents inside the parentheses U(9) always is a digit then put a double minus in front of the MID function.
Reply With Quote
  #5  
Old 05-05-2016, 08:25 AM
sglandon sglandon is offline LOOKUP - Complex lookup with 2 lookups in 1 cell Windows 7 64bit LOOKUP - Complex lookup with 2 lookups in 1 cell Office 2010 32bit
Novice
LOOKUP - Complex lookup with 2 lookups in 1 cell
 
Join Date: May 2016
Posts: 4
sglandon is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 05-05-2016, 09:17 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline LOOKUP - Complex lookup with 2 lookups in 1 cell Windows 7 64bit LOOKUP - Complex lookup with 2 lookups in 1 cell Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,505
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Quote:
Originally Posted by sglandon View Post
Is this still doable??? I'm guessing to use an expression search, "look at all characters before the first open parenthesis"?

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
Reply With Quote
  #7  
Old 05-05-2016, 09:44 AM
sglandon sglandon is offline LOOKUP - Complex lookup with 2 lookups in 1 cell Windows 7 64bit LOOKUP - Complex lookup with 2 lookups in 1 cell Office 2010 32bit
Novice
LOOKUP - Complex lookup with 2 lookups in 1 cell
 
Join Date: May 2016
Posts: 4
sglandon is on a distinguished road
Default

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!!!!!!!!!!!!!!!!!!!!!!!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
LOOKUP - Complex lookup with 2 lookups in 1 cell Lookup Tony Singh Excel 3 03-06-2015 11:03 AM
LOOKUP - Complex lookup with 2 lookups in 1 cell LOOKUP and IF FUNCTION midgetmogalle Excel 6 12-06-2013 09:35 AM
Lookup angie.chang Excel 1 07-27-2012 09:45 PM
LOOKUP - Complex lookup with 2 lookups in 1 cell Possible Lookup Karen222 Excel 3 01-10-2012 05:41 AM
LOOKUP - Complex lookup with 2 lookups in 1 cell LookUp aztiguen24 Excel 5 05-24-2011 03:57 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft