# Help! Vlook up Conundrum

#### eujin86

##### New Member
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

##### MrExcel MVP
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

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?

##### MrExcel MVP

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

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 CIDMYGOGC CIDMYGOGR CIDMYGOGS

<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?

##### MrExcel MVP

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 CIDMYGOGC CIDMYGOGR CIDMYGOGS

<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

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.

##### MrExcel MVP

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)

Give an example of X4 which fails?

#### eujin86

##### New Member

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 Channel (Level 1) Channel (Level 2) Characters CIDEMPGB 1 2 8 CIDEMPRM 3 4 8 CIDEMPSS 5 6 8 CIDMYGOGB Google Display 9 CIDMYGOGC Google Display 9 CIDMYGOGR 7 8 9 CIDMYGOGS Google Search 9

<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?

##### MrExcel MVP

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 Channel (Level 1) Channel (Level 2) Characters CIDEMPGB 1 2 8 CIDEMPRM 3 4 8 CIDEMPSS 5 6 8 CIDMYGOGB Google Display 9 CIDMYGOGC Google Display 9 CIDMYGOGR 7 8 9 CIDMYGOGS Google Search 9

<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?

Replies
0
Views
255
Replies
0
Views
180
Replies
3
Views
333
Replies
0
Views
178
Replies
1
Views
778

1,172,166
Messages
5,879,428
Members
433,429
Latest member
Ever

### 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?

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