Help! Vlook up Conundrum

[eujin86]

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

 

[Aladin Akyurek]

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]

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]

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]

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]

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]

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.

 

[Aladin Akyurek]

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)

Give an example of X4 which fails?

 

[eujin86]

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]

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?