Vlookup only the last 6 characters
 

I have a spreadsheet starting in cell A4. I want to lookup only the last 6
digits in column A and then lookup the value in column J and return the
appropriate value from column K.

The values in column A are 000322341; 000333456; etc.

The values in column J are 322341; 333456; etc.

Here is my current non working formula...
=VLOOKUP(RIGHT(A4,6),$J$4:$K$7,2,0)

Any help would be appreciated. Thanks.

Answer: Vlookup only the last 6 characters
 

Hi there! It looks like you're trying to use the function in Excel to lookup values based on the last 6 characters in a cell. Your formula is close, but you just need to make a small adjustment.

Instead of using the function to extract the last 6 characters, you can use the function to extract characters from the middle of a string. Here's the formula you can use:

1. Formula:

   ---

   =VLOOKUP(MID(A4,LEN(A4)-5,6),$J$4:$K$7,2,FALSE) 


   ---

Let me break down this formula for you:

1. MID(A4,LEN(A4)-5,6) extracts 6 characters starting from the 6th character from the end of the string in cell A4. The
   Formula:

   ---

   LEN 


   ---

   function returns the length of the string, so
   Formula:

   ---

   LEN(A4)-5 


   ---

   gives you the position of the 6th character from the end, and 6 is the number of characters to extract.
2. $J$4:$K$7 is the range of cells you want to search for the lookup value.
3. 2 is the column number in the lookup range that contains the values you want to return.
4. FALSE means you want an exact match for the lookup value.

Make sure to adjust the lookup range to include all the values you want to search, and adjust the column number to match the column that contains the values you want to return.

Vlookup only the last 6 characters
 

The RIGHT function is returning a text value, and I'm guessing that the
values in column J are true numbers. So you'd need to covert the text back
to numbers in order to have the lookup work properly:
=VLOOKUP(VALUE(RIGHT(A4,6)),$J$4:$K$7,2,0)

"JoeP" wrote:

I have a spreadsheet starting in cell A4. I want to lookup only the last 6
digits in column A and then lookup the value in column J and return the
appropriate value from column K.

The values in column A are 000322341; 000333456; etc.

The values in column J are 322341; 333456; etc.

Here is my current non working formula...
=VLOOKUP(RIGHT(A4,6),$J$4:$K$7,2,0)

Any help would be appreciated. Thanks.

Vlookup only the last 6 characters
 

Fantastic - thank you so much.

"bapeltzer" wrote:

The RIGHT function is returning a text value, and I'm guessing that the
values in column J are true numbers. So you'd need to covert the text back
to numbers in order to have the lookup work properly:
=VLOOKUP(VALUE(RIGHT(A4,6)),$J$4:$K$7,2,0)

"JoeP" wrote:

I have a spreadsheet starting in cell A4. I want to lookup only the last 6
digits in column A and then lookup the value in column J and return the
appropriate value from column K.

The values in column A are 000322341; 000333456; etc.

The values in column J are 322341; 333456; etc.

Here is my current non working formula...
=VLOOKUP(RIGHT(A4,6),$J$4:$K$7,2,0)

Any help would be appreciated. Thanks.