1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help

skyh3ck · Jan 6, 2024

Hello guys

i have a data base where i want to first find the column header, 2nd in same column find a certain date, and 3rd return correponding date from same row 1st column.

So i have prepared one sample excel file

Sheet one - Calc - where user can enter the class and dates
Sheet two - Log - this log is where the data feeded each month regularly this is the file from where i have to retrieve the date

This is sheet one - where user will update the class and date of file, i want result from another sheet log where i have data feeded

calc.xlsx

A

B

C

D

1

2

Class

E1

5

3

Date of Birth

01-Jan-2000

4

Date of File

01 Jan 2005

5

Date of Approval

01-Feb-2007

6

Date of Current

5

7

8

9

10

Calc

Cell Formulas
Range	Formula
C2	C2	=MATCH(B2, Log!B1:L1,0)
B6	B6	=MATCH(B2, Log!B1:L1,0)

This is the second sheet or tab where data is feeded - so whatever class is selected, the formula should first go in that column and check when the date of file crossed, and give me the date of current for that particular date of file. However one catch - some time in some case, date of file can go back, as marked in red, in that case any previous available date gets ignore, unless new date of file is not crossed in same column.

calc.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Date of Current

A1

B1

C1

D1

E1

F1

G1

H1

exRes

Other1

Other2

2

01-Jan-2020

05-Feb-1999

01-Jan-1999

05-Feb-1999

3

01-Feb-2020

08-Aug-1999

C = Continue previous date

4

01-Mar-2020

01-Dec-1999

U = Unqualified

5

01-Apr-2020

05-Jan-2000

RED = retrogessed or went to previous available date

6

01-May-2020

06-Mar-2000

01-Jan-1999

06-Mar-2000

7

01-Jun-2020

15-May-2001

8

01-Jul-2020

20-May-2001

9

01-Aug-2020

02-Oct-2003

10

01-Sep-2020

11-Nov-2003

08-Jan-2002

11-Nov-2003

10-Oct-2003

11-Nov-2003

11

01-Oct-2020

05-Dec-2003

12

01-Nov-2020

08-Feb-2005

08-Feb-2004

13

01-Dec-2020

16-Mar-2005

14

01-Jan-2021

01-Jan-2001

15

01-Feb-2021

20-May-2005

16

01-Mar-2021

18-Jun-2005

17

01-Apr-2021

20-Sep-2005

18

01-May-2021

01-Oct-2005

18-Nov-2005

01-Oct-2005

19

01-Jun-2021

01-Dec-2005

20

01-Jul-2021

01-Jan-2006

21

01-Aug-2021

01-Jan-2006

C

01-Jan-2006

22

01-Sep-2021

01-Jan-2006

01-Jan-2005

01-Jan-2006

C

01-Jan-2006

23

01-Oct-2021

01-Jan-2006

C

01-Jan-2006

24

01-Nov-2021

01-Jan-2006

C

01-Jan-2006

25

01-Dec-2021

01-Jan-2006

05-Mar-2006

01-Jan-2006

C

01-Jan-2006

26

01-Jan-2022

01-Jan-2006

C

01-Jan-2006

27

01-Feb-2022

01-Jan-2006

C

01-Jan-2006

28

01-Mar-2022

01-Jan-2006

C

01-Jan-2006

29

01-Apr-2022

01-Jan-2006

08-Aug-2006

01-Jan-2006

C

01-Jan-2006

30

01-May-2022

01-Jan-2006

C

01-Jan-2006

31

01-Jun-2022

01-Jan-2006

01-Dec-2006

01-Jan-2006

C

01-Jan-2006

32

01-Jul-2022

01-Jan-2006

31-Dec-2006

01-Jan-2006

C

01-Jan-2006

33

01-Aug-2022

10-Feb-2007

05-Feb-07

34

01-Sep-2022

10-Feb-2007

C

10-Feb-2007

C

10-Feb-2007

35

01-Oct-2022

10-Feb-2007

C

10-Feb-2007

C

10-Feb-2007

36

01-Nov-2022

10-Feb-2007

15-Feb-2007

C

10-Feb-2007

C

10-Feb-2007

37

01-Dec-2022

10-Feb-2007

C

10-Feb-2007

C

10-Feb-2007

38

01-Jan-2023

10-Feb-2007

C

10-Feb-2007

C

10-Feb-2007

39

01-Feb-2023

10-Feb-2007

C

10-Feb-2007

C

10-Feb-2007

40

01-Mar-2023

10-Feb-2007

C

10-Feb-2007

C

10-Feb-2007

03-Mar-08

41

01-Apr-2023

10-Feb-2007

C

10-Feb-2007

C

10-Feb-2007

42

01-May-2023

01-Jan-2005

43

01-Jun-2023

01-Feb-2005

44

01-Jul-2023

15-Mar-2007

45

01-Aug-2023

08-Aug-2007

18-Aug-2008

08-Aug-2007

46

01-Sep-2023

15-Sep-2007

47

01-Oct-2023

01-Dec-2007

U

01-Dec-2007

48

01-Nov-2023

05-Jan-2008

49

01-Dec-2023

05-Jan-2010

06-Feb-2011

05-Jan-2008

50

01-Jan-2024

05-Feb-2015

02-Feb-2010

01-Feb-2007

05-Jan-2008

U

01-Jan-2016

U

05-Jan-2008

05-Aug-2011

01-Jan-23

51

01-Feb-2024

52

01-Mar-2024

53

01-Apr-2024

54

01-May-2024

55

01-Jun-2024

56

01-Jul-2024

57

01-Aug-2024

58

01-Sep-2024

59

01-Oct-2024

60

01-Nov-2024

61

01-Dec-2024

62

01-Jan-2025

63

01-Feb-2025

64

01-Mar-2025

65

01-Apr-2025

66

01-May-2025

67

01-Jun-2025

68

01-Jul-2025

69

01-Aug-2025

70

01-Sep-2025

71

01-Oct-2025

72

01-Nov-2025

73

01-Dec-2025

74

01-Jan-2026

75

01-Feb-2026

76

01-Mar-2026

77

01-Apr-2026

78

01-May-2026

79

01-Jun-2026

80

01-Jul-2026

81

01-Aug-2026

82

01-Sep-2026

83

01-Oct-2026

84

01-Nov-2026

85

01-Dec-2026

86

01-Jan-2027

87

01-Feb-2027

88

01-Mar-2027

89

01-Apr-2027

90

01-May-2027

91

01-Jun-2027

92

01-Jul-2027

93

01-Aug-2027

94

01-Sep-2027

95

01-Oct-2027

96

01-Nov-2027

97

01-Dec-2027

98

01-Jan-2028

99

01-Feb-2028

100

01-Mar-2028

101

01-Apr-2028

102

01-May-2028

103

01-Jun-2028

104

01-Jul-2028

105

01-Aug-2028

106

01-Sep-2028

107

01-Oct-2028

108

01-Nov-2028

109

01-Dec-2028

110

01-Jan-2029

111

01-Feb-2029

112

01-Mar-2029

113

01-Apr-2029

114

01-May-2029

115

01-Jun-2029

116

01-Jul-2029

117

01-Aug-2029

118

01-Sep-2029

119

01-Oct-2029

120

01-Nov-2029

121

01-Dec-2029

122

01-Jan-2030

123

01-Feb-2030

124

01-Mar-2030

125

01-Apr-2030

126

01-May-2030

127

01-Jun-2030

128

01-Jul-2030

129

01-Aug-2030

130

01-Sep-2030

131

01-Oct-2030

132

01-Nov-2030

133

01-Dec-2030

Log

GraH · Jan 7, 2024

Crossposted