vlook up Function
 

Hi everyone....
In Excel we have data in sheet 1 as follows
A B C D E
1 Index No. Date Blank Code
Reference
2 1208-2134706406 12/19/2008 0100 G
11/11/08-12/12/08
3 1208-2134706406 12/19/2008 0100 E
11/11/08-12/12/08
4 1208-2134726210 12/18/2008 0100 G
11/11/08-12/12/08
5 1208-2134726210 12/18/2008 0100 E
11/11/08-12/12/08

F G H I
J
1 Relation Relation code Amount Discount net
amount
2 R 70003-00 1,256.32 0.00
1,256.32
3 R 70002-00 1,755.64 0.00
1,755.64
4 R 70003-00 500.76 0.00
500.76
5 R 70002-00 780.75 0.00
780.75

K L
1 R. Date Target
2
3 12/23/2008 1223
4 12/24/2008 4558
5


In Sheet 2, we have to fill up data specifically for Reference starting with
"E"
A B C D E F
1 Index No. Start Date End Date Blank Blank
Target
2 1208-2134706406
3 1208-2134726210



In Sheet 3, we have to fill up data specifically for Reference starting with
"G"

A B C D E F
1 Index No. Start Date End Date Blank Blank
Target
2 1208-2134726210
3 1208-2134706406


The Sheet 2 & 3 are specific, the Sheet 2 contains details for Reference
starting with E, And Sheet 3 contains details of Reference starting with G.
In Column E, in sheet 1, €˜Reference, has details such as Reference (E or G)
and the start date and end date separated by €˜-€˜.

All we need is a V look up formula, to automatically fill in Column F in
Sheet 2 & 3 respectively for Reference (E & G) then Start date in Column B,
and End date in Column C.

We tried the following formula, but could not work properly.
=VLOOKUP($A$2:$A$3,Sheet1!$A$1:$L$5,12,0)

The other data that we tried was having If Condition i.e.
=IF(LEFT(Sheet1!E2,2="E "),VLOOKUP(Sheet2!A2:A3,Sheet1!A1:L5,12,0),"No
target")

Please help me.
Thanks.

vlook up Function
 

Hi K****ij,

I think that is not a standard function availeble for your problem,
In Excel2003 I have created 2 functions for you:


Public Function K****ijStart(AnIndex As String, _
AllData As Range, _
CodeStart As String) As String
On Local Error GoTo K****ijStart_err

Dim intLoop As Integer

For intLoop = 1 To AllData.Rows.Count
If AllData.Cells(intLoop, 1).Value = AnIndex Then
If Left(AllData.Cells(intLoop, 5).Value, 1) _
= CodeStart Then
K****ijStart _
= Mid(AllData.Cells(intLoop, 5), 3, 8)
Exit For
End If
End If
Next

GoTo K****ijStart_exit

K****ijStart_err:
K****ijStart = "Error"

K****ijStart_exit:
End Function

Public Function K****ijEnd(AnIndex As String, _
AllData As Range, _
CodeStart As String) As String
On Local Error GoTo K****ijEnd_err

Dim intLoop As Integer

For intLoop = 1 To AllData.Rows.Count
If AllData.Cells(intLoop, 1).Value = AnIndex Then
If Left(AllData.Cells(intLoop, 5).Value, 1) _
= CodeStart Then
K****ijEnd = _
Right(AllData.Cells(intLoop, 5).Value, 8)
Exit For
End If
End If
Next

GoTo K****ijEnd_exit

K****ijEnd_err:
K****ijEnd = "Error"

K****ijEnd_exit:
End Function

The first will give the start date the second the end date of the
period.

Both expect 3 parameters:

1) The indexno. to look for
2) The data range to look in
3) A letter "E" or "G"

Sample for cell B2 on Sheet2:
=K****itijStartDate(A2;Sheet1!$A$2:$E$5;"E")

Sample for celll C3 on Sheet3:
=K****ijEnd(A3;Sheet1!$A$2:$E$5;"G")


HTH,

Wouter