VLOOKUP with LEFT and RIGHT Function

S

SAXON10

Board Regular
Joined
Jun 1, 2017
Messages
109
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


[TABLE="width: 540"]
<colgroup><col span="2"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]DESIRED RESULT

A B C [/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]DATA

F G

[/TD]
[/TR]
[TR]
[TD]CODE[/TD]
[TD]RESULT[/TD]
[TD]FORMULA REQUIRED[/TD]
[TD][/TD]
[TD][/TD]
[TD]CODE[/TD]
[TD]TYPE[/TD]
[/TR]
[TR]
[TD]3187[/TD]
[TD]K16[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003187[/TD]
[TD]16KPY[/TD]
[/TR]
[TR]
[TD]3196[/TD]
[TD]K16[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003196[/TD]
[TD]16KPY[/TD]
[/TR]
[TR]
[TD]3135[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003135[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]NONE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003206[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]NONE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100002853[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3208[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003208[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3206[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003139[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3139[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003154[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3154[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003159[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3159[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003213[/TD]
[TD]16KPY[/TD]
[/TR]
[TR]
[TD]3199[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3133[/TD]
[TD]K16[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
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?
 
Upvote 0
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.
 
Upvote 0
Eric W said:
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.
Click to expand...


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.
 
Upvote 0
Modifying Eric's formulas a little ..


Book1
ABCDEFG
1
2CODERESULTCODETYPE
33187K16K1610000318716KPY
43196K16K1610000319616KPY
53135K18K1810000313518KPY
6NONE10000320618KPY
7NONE10000285318KPY
83208K18K1810000320818KPY
93206K18K1810000313918KPY
103139K18K1810000315418KPY
113154K18K1810000315918KPY
123159K18K1810000321316KPY
133199
143133
Lookup
Cell Formulas
RangeFormula
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.
 
Upvote 0
Peter_SSs said:
Modifying Eric's formulas a little ..

ABCDEFG
CODERESULTCODETYPE
K16K1616KPY
K16K1616KPY
K18K1818KPY
NONE18KPY
NONE18KPY
K18K1818KPY
K18K1818KPY
K18K1818KPY
K18K1818KPY
K18K1816KPY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3187[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100003187[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3196[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100003196[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3135[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100003135[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100003206[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100002853[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3208[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100003208[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3206[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100003139[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3139[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100003154[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3154[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100003159[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3159[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100003213[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]3199[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]3133[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Lookup​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=IFERROR("K"&LEFT(LOOKUP(2^15,FIND(RIGHT(A3,4),$F$3:$F$12),$G$3:$G$12),2),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]{=IFERROR("K"&LEFT(VLOOKUP("*"&RIGHT(A3,4),$F$3:$G$12&"",2,0),2),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Enter without the {} but confirm with Ctrl+Shift+Enter, not just Enter
If entered correctly, Excel will insert the {}[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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?
 
Upvote 0
SAXON10 said:
Please look at column A15 is blank but both formula return is K16
Click to expand...
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.
 
Last edited:
Upvote 0
Peter_SSs said:
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.
Click to expand...

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

Your are legend
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,023
Latest member
alabaz

Share this page

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
Back
Top