Vlookup on middle 2 digits of a 4 digit number

[rfinnegan]

Hi All:

I'd like to perform a vlookup on the middle 2 digits of a 4 digit number. For example, I get a report that lists an employee number as 9341, when in fact the employee number is 34.

How can I have my vlookup only look at the 2 middle digits? Or another approach, ignore the first and last digit?

For what it's worth, the number the vlookup will look at almost always starts with a 9 (like the example above). And is, so far always a 4 digit number.

I'm using excel 2003 also.

Thanks in advance.

 

[Weaver]

if your 4 digit no is in A1 then

=INT(MOD(A1,1000)/10)

Just use that formula as the first argument of your vlookup

 

[mvptomlinson]

Or..

=VLOOKUP(MID(A1,2,2),...

 

[rfinnegan]

Hi All:

I couldn't get either to work....then I realized they didn't work because I asked the wrong question.

What I have is the middle 2 numbers (the employee number) and I need to find it in a series of 4 digit numbers.

So using the example in the original post, my vlookup needs to find 34 when the number it is inbedded in is 9341.

Thanks again.

PS I tried to modify both the solutions that were submitted....I couldn't get it to work.

 

[b13]

Or..

=VLOOKUP(MID(A1,2,2),...

How about a combination of numbers and character, for example ( 311 Crew wages). I need the 311 to as my source reference for my vlookup. Instead of creating a different cell besides that title and only the 311 is indicated.

Thanks a lot.

 

[FryGirl]

In the table where you are looking up the 9341, why not add a column to the left of 9341 and use the Mid function to just extract the 34

MID(A2,2,2)+0

 

[John Davis]

Hi All:

I couldn't get either to work....then I realized they didn't work because I asked the wrong question.

What I have is the middle 2 numbers (the employee number) and I need to find it in a series of 4 digit numbers.

So using the example in the original post, my vlookup needs to find 34 when the number it is inbedded in is 9341.

Thanks again.

PS I tried to modify both the solutions that were submitted....I couldn't get it to work.

Peter Moran's VLOOKUPNTH FUNCTION CAN ACHIEVE THIS

Function VLOOKUPNTH(lookup_value, table_array As Range, _
           col_index_num As Integer, nth_value)
' Extension to VLOOKUP function.  Allows for finding
' the "nth" item that matches the lookup value.
' 2/1/05 - Modified to handle Wildcards - "=" changed to "like"

Dim nRow As Long
Dim nVal As Integer
Dim bFound As Boolean
  VLOOKUPNTH = "Not Found"
  With table_array
    For nRow = 1 To .Rows.Count
      If .Cells(nRow, 1).Value Like lookup_value Then
      'If .Cells(nRow, 1).Value = lookup_value Then
        nVal = nVal + 1
      End If
      If nVal = nth_value Then
        VLOOKUPNTH = .Cells(nRow, col_index_num).Text
        Exit Function
      End If
    Next nRow
  End With
End Function

You'll need to use a wildcard "*"&A1&"*"