Index match ignore colon in time

[ste33uka]

Hi is it possible to index match but ignore colon in time ?
Have added screen shot for better understanding
Thanks

Book1
	A	B	C	D	E	F	G	H	I
1				12:00	1200	999	1
2				13:15	1315	999	2
3				14:00	1400	999	3
4				14:15	1415	999	4
5
6
7
8
9
10
11
12
Sheet1

Cell Formulas
Range	Formula
F1:F4	F1	=IFERROR(INDEX($G$1:$G$999,MATCH(E1,SUBSTITUTE($D$1:$D$999,":",""),0)),999)

 

[anand3dinesh]

Hi Please explain me bit more. what is the result you looking for

 

[ste33uka]

formula in f1 would = 1

 

[FormR]

Hi, here's one option you can try.

Book1
	A	B	C	D	E	F	G
1				12:00:00	1200	1	1
2				13:15:00	1315	2	2
3				14:00:00	1400	3	3
4				14:15:00	1415	4	4
Sheet1

Cell Formulas
Range	Formula
F1:F4	F1	=IFERROR(INDEX($G$1:$G$999,MATCH(0+TEXT(E1,"00\:00"),$D$1:$D$999,0)),999)

 

[ste33uka]

Hi, here's one option you can try.

Book1
	A	B	C	D	E	F	G
1				12:00:00	1200	1	1
2				13:15:00	1315	2	2
3				14:00:00	1400	3	3
4				14:15:00	1415	4	4
Sheet1

Cell Formulas
Range	Formula
F1:F4	F1	=IFERROR(INDEX($G$1:$G$999,MATCH(0+TEXT(E1,"00\:00"),$D$1:$D$999,0)),999)

Thanks, but wont work for sum reason

 

[maabadi]

You want to compare time with numbers. I think.
Use this:

=IFERROR(INDEX($G$1:$G$999,MATCH(HOUR(D1)*100+MINUTE(D1),$E$1:E1,0)),999)

 

[FormR]

wont work for sum reason

Hi, that's not really enough information to help you work out what might be going wrong.

 

[ste33uka]

Thanks both got it working now, thanks again