VLOOKUP with LEFT and RIGHT Function

[SAXON10]

Hi,

I am trying to combine the Vlookup, Left and Right function in one cell.

Column F3:F12 contain of number and G3:G12 is type and Column A is a lookup value for last four characters (A3:A14).

I am apply the following formula in C3="K"&LEFT(VLOOKUP(RIGHT(--$A3,4)&"*",$F$3:$G$12,2,0),2) based on the below

data but I am getting error.

Any help would be appreciated

DESIRED RESULT A B C			DATA F G
CODE	RESULT	FORMULA REQUIRED			CODE	TYPE
3187	K16				100003187	16KPY
3196	K16				100003196	16KPY
3135	K18				100003135	18KPY
NONE					100003206	18KPY
NONE					100002853	18KPY
3208	K18				100003208	18KPY
3206	K18				100003139	18KPY
3139	K18				100003154	18KPY
3154	K18				100003159	18KPY
3159	K18				100003213	16KPY
3199	K18
3133	K16

<colgroup><col span="2"><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[Norie]

Do you want to look for the values in column A in column F and return a value, if there is a match, from column G?

 

[SAXON10]

Do you want to look for the values in column A in column F and return a value, if there is a match, from column G?

Thanks for your reply. I want formula in column C3 based on the data from F and G.

 

[Eric W]

Try:

=VLOOKUP("*"&RIGHT(A3,4),$F$3:$G$12&"",2,0)
with Control+Shift+Enter.

Or:

=LOOKUP(2^15,FIND(RIGHT(A3,4),$F$3:$F$12),$G$3:$G$12)

if you don't want to use Control+Shift+Enter. Put IFERROR around either one if you don't want to see #N/A for no matches.

 

[SAXON10]

Try:

=VLOOKUP("*"&RIGHT(A3,4),$F$3:$G$12&"",2,0)
with Control+Shift+Enter.

Or:

=LOOKUP(2^15,FIND(RIGHT(A3,4),$F$3:$F$12),$G$3:$G$12)

if you don't want to use Control+Shift+Enter. Put IFERROR around either one if you don't want to see #N/A for no matches.

https://www.dropbox.com/s/pdr5qxcqfnjjxy0/DD.xlsx?dl=0
https://www.dropbox.com/s/vpptmotnnuz6x71/MM.PNG?dl=0

Thank for your help. Formula working fine but if blanks column A3 value return something.

Please assist me.

 

[Peter_SSs]

Modifying Eric's formulas a little ..

Book1
	A	B	C	D	E	F	G
1
2	CODE	RESULT				CODE	TYPE
3	3187	K16	K16			100003187	16KPY
4	3196	K16	K16			100003196	16KPY
5	3135	K18	K18			100003135	18KPY
6	NONE					100003206	18KPY
7	NONE					100002853	18KPY
8	3208	K18	K18			100003208	18KPY
9	3206	K18	K18			100003139	18KPY
10	3139	K18	K18			100003154	18KPY
11	3154	K18	K18			100003159	18KPY
12	3159	K18	K18			100003213	16KPY
13	3199
14	3133
Lookup

Cell Formulas
Range	Formula
C3	=IFERROR("K"&LEFT(LOOKUP(2^15,FIND(RIGHT(A3,4),$F$3:$F$12),$G$3:$G$12),2),"")
B3	{=IFERROR("K"&LEFT(VLOOKUP("*"&RIGHT(A3,4),$F$3:$G$12&"",2,0),2),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[SAXON10]

Modifying Eric's formulas a little ..

	A	B	C	D	E	F	G
1
2	CODE	RESULT				CODE	TYPE
3	3187	K16	K16			100003187	16KPY
4	3196	K16	K16			100003196	16KPY
5	3135	K18	K18			100003135	18KPY
6	NONE					100003206	18KPY
7	NONE					100002853	18KPY
8	3208	K18	K18			100003208	18KPY
9	3206	K18	K18			100003139	18KPY
10	3139	K18	K18			100003154	18KPY
11	3154	K18	K18			100003159	18KPY
12	3159	K18	K18			100003213	16KPY
13	3199
14	3133

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Lookup​

Worksheet Formulas Cell Formula C3 =IFERROR("K"&LEFT(LOOKUP(2^15,FIND(RIGHT(A3,4),$F$3:$F$12),$G$3:$G$12),2),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B3 {=IFERROR("K"&LEFT(VLOOKUP("*"&RIGHT(A3,4),$F$3:$G$12&"",2,0),2),"")} <thead> </thead><tbody> </tbody> Enter without the {} but confirm with Ctrl+Shift+Enter, not just Enter If entered correctly, Excel will insert the {}

<tbody>
</tbody>

Thanks for your reply and help. When lookup range is blank formula return the value.

Please look at column A15 is blank but both formula return is K16

How to resolve the issue?

 

[Peter_SSs]

Please look at column A15 is blank but both formula return is K16

Sorry, I missed that in your image.

=IF(A3="","",IFERROR("K"&LEFT(VLOOKUP("*"&RIGHT(A3,4),$F$3:$G$12&"",2,0),2),"")) (Ctrl+Shift+Enter)

=IF(A3="","",IFERROR("K"&LEFT(LOOKUP(2^15,FIND(RIGHT(A3,4),$F$3:$F$12),$G$3:$G$12),2),""))


BTW, best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.

 

[SAXON10]

Sorry, I missed that in your image.

=IF(A3="","",IFERROR("K"&LEFT(VLOOKUP("*"&RIGHT(A3,4),$F$3:$G$12&"",2,0),2),"")) (Ctrl+Shift+Enter)

=IF(A3="","",IFERROR("K"&LEFT(LOOKUP(2^15,FIND(RIGHT(A3,4),$F$3:$F$12),$G$3:$G$12),2),""))


BTW, best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.

Thank you so much for your help, formula working fine way.

Your are legend

 

[Peter_SSs]

Thank you so much for your help, formula working fine way.

Your are legend

Glad you got a successful outcome.
Eric did most of the work, I just tweaked his formulas a bit.