# Vlookup Problem

Hi Guys, I have a Vlookup on an Excel Cell containing several IDs. I use the look up on the Previous Day's speadsheet to see what the position is on the trades. See below

15th of Oct
GBO ID
1909843.28, 2222646.28, 2222836.28, 2224190.28, 2224281.28
16th of Oct
GBO ID
2224281.28, 1909843.28, 2222646.28, 2222836.28, 2224190.28
The Problem is the sequence of the trades IDs randomly switches from one day to the next due to a system bug so my Vlookup does not work. Is there a way that irrespective of any change in the order of the trade ID, the Vlookup could still work?
###### Who is Participating?

Commented:
I agree with ssaqibh that a formula solution will be difficult.
With 5 ID numbers there are 120 combinations for the numbers position in the string.

Not clear if you have 4 or 5 ID numbers in the string, or if the ID numbers end with .28.
I have made an example file with some test ID numbers with 5 ID numbers in each string, each with 7 digits.
I guess the format is fixed, so it is overkill to make a solution for variable number of ID and variable ID format. Easy changed when the format is known.
The formula is in column A on the sheet Today, looking for a match for column B, on the sheet Previous, column B, with result in column C.

Here is the code for the function

``````Option Explicit

Function FindPreviousID(x As String) As Variant
Application.Volatile
'Application.Volatile makes the function run on every calculation, must be first line in function

Dim wsPrevious As Worksheet
Dim RowNo As Long
Dim arrXvalues(5) As String
Dim PreviousRecord As String
Dim i As Integer
Dim Found As Integer
Dim MatchID As Integer

Set wsPrevious = Worksheets("Previous")

'Put the ID numbers in array
For i = 1 To 5
arrXvalues(i) = Mid(x, 9 * (i - 1) + 1, 7)
Next i

'Loop the previous day values
RowNo = 0
Found = 0
Do
RowNo = RowNo + 1
MatchID = 0

'Get the record from the previous day in column B
PreviousRecord = wsPrevious.Cells(RowNo, 2)

'Check if ID number can be found in record
For i = 1 To 5
If InStr(1, PreviousRecord, arrXvalues(i)) > 0 Then
MatchID = MatchID + 1
End If
Next i

'Match if all 5 ID numbers are in the previous record
If MatchID = 5 Then
Found = 1
End If
Loop Until Found = 1 Or RowNo >= wsPrevious.UsedRange.Rows.Count

'Result is value in column C, or no result
If Found = 1 Then
FindPreviousID = wsPrevious.Cells(RowNo, 3)
Else
FindPreviousID = "No match"
End If
End Function
``````
Special-Vlookup-function.xlsm
0

EngineerCommented:
I am not clear on your problem. Can you explain more possibly with an example showing how your vlookup works?
0

Author Commented:
Ok. Its just a standard Vlookup on 1 Excel Cell on the change in position of a shareholding in Esso. The problem is within the Excel Cell are 4 Numbered trade Ids, but they switch in sequence from 1 day to the next so (due to system bug) , if you do a Vlookup on yesterday's position in Esso, it won't work
The Excel Cell on the 15th reads 1909843, 2222646, 2222836, 2224190 2224281

The Excel Cell on the 16th  reads 2224281, 1909843, 2222646, 2222836, 2224190
0

EngineerCommented:
I am sorry I am still blank. Can you give me the formula you are using? Which is the value looked up, where is it looked up?
0

Author Commented:
I am using Vlookup(B3, B:C, 2, False) from Previous Day Excel Rec.

Cell B3 contains 4 trade Ids (1909843, 2222646,2224190,2224281)
Cell B3 the next day contains the same 4 ids but in a different order (2224281,2224190,2222646,1909843) thus the Vlookup fails.I need to know
a formula whereby it can still do a Vlookup even if the digits have changed order.
0

EngineerCommented:
That sounds horrible if i have understood you correctly. It may not be impossible but would surely be next to it. I wonder if someone can have a go at it. The only way I can foresee this being done is through VBA and it is not going to look elegant.

Can you build a small file which I can use for experimenting?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.