Help! Vlook up Conundrum

eujin86

New Member
Joined
Dec 10, 2013
Messages
14
Hi All,

Would need your help on this!

I got the data as below:

Data
CIDEMPRM1
CIDEMPRM2

<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

What I want to do is to vlook-up both data above with a summary table. I have tried to use CIDEMPRM? in the summary table but couldn't get a result (e.g.: #N/A).

Please advice how I can solve this. My thinking is to have the summary table with CIDEMPRM and the last character can be anything so that when vlookup, any numerical numbers can be identified via the summary table.

Hope that is clear!

Thanks!

Eujin
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Hi All,

Would need your help on this!

I got the data as below:

Data
CIDEMPRM1
CIDEMPRM2

<TBODY>
</TBODY>

What I want to do is to vlook-up both data above with a summary table. I have tried to use CIDEMPRM? in the summary table but couldn't get a result (e.g.: #N/A).

Please advice how I can solve this. My thinking is to have the summary table with CIDEMPRM and the last character can be anything so that when vlookup, any numerical numbers can be identified via the summary table.

Hope that is clear!

Thanks!

Eujin

What is the formula you have tried?
 

eujin86

New Member
Joined
Dec 10, 2013
Messages
14
Hi Aladin,

The formulas I used is as below:

=LEFT($O4,9) to excerpt the long code (this will be the same as in a summary table that will be vlookup at)
=VLOOKUP($X4,'Summary List'!$A:$B,2,FALSE) - this is use to match and populate data if matches).


Thanks!

What is the formula you have tried?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Hi Aladin,

The formulas I used is as below:

=LEFT($O4,9) to excerpt the long code (this will be the same as in a summary table that will be vlookup at)
=VLOOKUP($X4,'Summary List'!$A:$B,2,FALSE) - this is use to match and populate data if matches).


Thanks!

If X4 house:

=LEFT($O4,9)

we can just have:

=VLOOKUP(LEFT($O4,9)&"*",'Summary List'!$A:$B,2,0)

Does this succeed?
 

eujin86

New Member
Joined
Dec 10, 2013
Messages
14

ADVERTISEMENT

Hi Aladin,

It works if X4 = LEFT($O4,8) and use =VLOOKUP(LEFT($O4,8)&"*",'Summary List'!$A:$B,2,0)

I would like to keep =LEFT($O4,9) but in the looking up summary list, how can I use CIDEMPRM* or CIDEMPRM? or any applicable to have it remains 9 character (this is due to some other codes that need to be distinct by minimum 9 characters in summary list)

e.g.:
CIDMYGOGB</SPAN>
CIDMYGOGC</SPAN>
CIDMYGOGR</SPAN>
CIDMYGOGS</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>


Appreciate your help Aladin and anyone out there!!! Thanks!


If X4 house:

=LEFT($O4,9)

we can just have:

=VLOOKUP(LEFT($O4,9)&"*",'Summary List'!$A:$B,2,0)

Does this succeed?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Hi Aladin,

It works if X4 = LEFT($O4,8) and use =VLOOKUP(LEFT($O4,8)&"*",'Summary List'!$A:$B,2,0)

I would like to keep =LEFT($O4,9) but in the looking up summary list, how can I use CIDEMPRM* or CIDEMPRM? or any applicable to have it remains 9 character (this is due to some other codes that need to be distinct by minimum 9 characters in summary list)

e.g.:
CIDMYGOGB</SPAN>
CIDMYGOGC</SPAN>
CIDMYGOGR</SPAN>
CIDMYGOGS</SPAN>

<TBODY>
</TBODY>


Appreciate your help Aladin and anyone out there!!! Thanks!

=VLOOKUP($X4&"*",'Summary List'!$A:$B,2,0)

This looks up X4 on Summary in A and returns a result from B.
 

eujin86

New Member
Joined
Dec 10, 2013
Messages
14

ADVERTISEMENT

Hi Aladin,

It is still returning N/A.

So what can I do in the Summary List because it's only 8 characters now - CIDEMPRM (for example)

=VLOOKUP($X4&"*",'Summary List'!$A:$B,2,0)

This looks up X4 on Summary in A and returns a result from B.
 

eujin86

New Member
Joined
Dec 10, 2013
Messages
14
Hi Aladin,

This is the X4 formula =LEFT($O4,9)
This is the Y4 formula that returns #N/A =VLOOKUP($X4&"*",'Summary List'!$A:$B,2,0)

This is the lookup table from Summary List sheet:

Abbrevation</SPAN>Channel (Level 1)</SPAN>Channel (Level 2)</SPAN>Characters</SPAN>
CIDEMPGB</SPAN>1</SPAN>2</SPAN>8</SPAN>
CIDEMPRM</SPAN>3</SPAN>4</SPAN>8</SPAN>
CIDEMPSS</SPAN>5</SPAN>6</SPAN>8</SPAN>
CIDMYGOGB</SPAN>Google </SPAN>Display</SPAN>9</SPAN>
CIDMYGOGC</SPAN>Google </SPAN>Display</SPAN>9</SPAN>
CIDMYGOGR</SPAN>7</SPAN>8</SPAN>9</SPAN>
CIDMYGOGS</SPAN>Google </SPAN>Search</SPAN>9</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=2><COL></COLGROUP>

What I would like to do is in the summary list, how can i make those with 8 characters be read by the vlookup formula above? e.g. Getting it to 9 characters so that differentiation on the other codes can be maintained via vlookup.

Hope its clearer what I am trying to achieve. Appreciate your advice!

Give an example of X4 which fails?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Hi Aladin,

This is the X4 formula =LEFT($O4,9)
This is the Y4 formula that returns #N/A =VLOOKUP($X4&"*",'Summary List'!$A:$B,2,0)

This is the lookup table from Summary List sheet:

Abbrevation</SPAN>
Channel (Level 1)</SPAN>
Channel (Level 2)</SPAN>
Characters</SPAN>
CIDEMPGB</SPAN>
1</SPAN>
2</SPAN>
8</SPAN>
CIDEMPRM</SPAN>
3</SPAN>
4</SPAN>
8</SPAN>
CIDEMPSS</SPAN>
5</SPAN>
6</SPAN>
8</SPAN>
CIDMYGOGB</SPAN>
Google </SPAN>
Display</SPAN>
9</SPAN>
CIDMYGOGC</SPAN>
Google </SPAN>
Display</SPAN>
9</SPAN>
CIDMYGOGR</SPAN>
7</SPAN>
8</SPAN>
9</SPAN>
CIDMYGOGS</SPAN>
Google </SPAN>
Search</SPAN>
9</SPAN>

<TBODY>
</TBODY>

What I would like to do is in the summary list, how can i make those with 8 characters be read by the vlookup formula above? e.g. Getting it to 9 characters so that differentiation on the other codes can be maintained via vlookup.

Hope its clearer what I am trying to achieve. Appreciate your advice!

Would you post some example values that can occur in O4?
 

Forum statistics

Threads
1,136,353
Messages
5,675,288
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top