vlookup true range lookup

[scorpidxls]

Dear all,

I have a table of date and time with group name, and I want to lookup a series of date and time to that table. the table is something like this :

i want to lookup to this table, for instance if one of the data is 02/05/24 12:15:01 then it will return the group code of 4 (using the TRUE range lookup). But I also wanted, if the time isn't in the table range, for instance 02/05/24 17:01:00, it will return "NOT IN GROUP".
If I only using vlookup and using TRUE range lookup, the result for this 02/05/24 17:01:00 will be group code of 6 (because it's still in range between 02/05/24 15:45 and 03/05/24 11:09) but what I wanted is it will return in result "NOT IN GROUP", any suggestion for the formula that I could use without modifying the table? thanks

 

[etaf]

with the TRUE option it will always find the nearest , and so
02/05/24 12:15:01
which is not in table
so it will lookup the nearest lower option - which is
02/05/24 11:51:00
and so return group 4
and as 02/05/24 12:15:01 IS NOT in table - thats what it does
the same with
02/05/24 17:01:00,
its NOT in table and so will then lookup
02/05/24 15:45:00
and so return 6

the result you are after - is if the lookup is greater than the max time for that date - to return not in group
in your example for
2/5/24

the max time is 15;45;00
so any lookup for 2/5/24 - which is greater than 15:45:00 , you want to return NOT in GROUP
and for dates
3/5/24
any time greater than 18:10:00 - will return NOT in Group

IS THAT CORRECT

maybe an IF statement that will combile the date and lookup the time as a Max
I'll have a think, but just wanted to make 100% sure i have the requirement correct

of course other members may know and answer

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[etaf]

how about
=IF(D2>MAX(FILTER($A$2:$A$6,INT($A$2:$A$6)=INT(D2))),"not in group", VLOOKUP(D2,$A$2:$B$6,2))

Book6
	A	B	C	D	E	F	G
1				Date	group - wrong result		Filter group correct result
2	5/2/24 10:30	1		5/2/24 18:45	6		not in group
3	5/2/24 10:49	1
4	5/2/24 15:45	6		5/2/24 12:15	1		1
5	5/3/24 11:09	10
6	5/3/24 18:10	13		5/3/24 19:34	13		not in group
7
8				5/3/24 17:00	10		10
Sheet1

Cell Formulas
Range	Formula
E2,E8,E6,E4	E2	=VLOOKUP(D2,$A$2:$B$6,2)
G2,G8,G6,G4	G2	=IF(D2>MAX(FILTER($A$2:$A$6,INT($A$2:$A$6)=INT(D2))),"not in group", VLOOKUP(D2,$A$2:$B$6,2))

 

[scorpidxls]

with the TRUE option it will always find the nearest , and so
02/05/24 12:15:01
which is not in table
so it will lookup the nearest lower option - which is
02/05/24 11:51:00
and so return group 4
and as 02/05/24 12:15:01 IS NOT in table - thats what it does
the same with
02/05/24 17:01:00,
its NOT in table and so will then lookup
02/05/24 15:45:00
and so return 6

the result you are after - is if the lookup is greater than the max time for that date - to return not in group
in your example for
2/5/24

the max time is 15;45;00
so any lookup for 2/5/24 - which is greater than 15:45:00 , you want to return NOT in GROUP
and for dates
3/5/24
any time greater than 18:10:00 - will return NOT in Group

IS THAT CORRECT

maybe an IF statement that will combile the date and lookup the time as a Max
I'll have a think, but just wanted to make 100% sure i have the requirement correct

of course other members may know and answer

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Thanks for your fast response, Sorry I haven't using the xl2bb before, here's the mini sheet for the table Im using :

Book2
	A	B	C	D	E	F
1	date&time	group		lookup entry
2	02/05/24 10:30:00	1		02/05/24 12:01	4
3	02/05/24 10:49:12	1		02/05/24 17:00	6	-> wanted the result to be "NO GROUP" because 17:00 at May 2nd is not on the table
4	02/05/24 10:51:00	2		03/05/24 08:00	6	-> wanted the result to be "NO GROUP" because 08:00 at May 3nd is not on the table
5	02/05/24 11:09:31	2
6	02/05/24 11:12:00	3
7	02/05/24 11:29:07	3
8	02/05/24 11:51:00	4
9	02/05/24 13:36:21	4
10	02/05/24 14:40:00	5
11	02/05/24 15:44:00	5
12	02/05/24 15:45:00	6
13	03/05/24 11:09:00	10
14	03/05/24 12:11:14	10
15	03/05/24 15:35:00	11
16	03/05/24 16:45:38	11
17	03/05/24 17:00:00	12
18	03/05/24 18:00:06	12
19	03/05/24 18:10:00	13
Sheet1

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP(D2,$A:$B,2)
E3:E4	E3	=VLOOKUP(D3,A:B,2)

and YES, your statement is right, I'm just adding the minimum time also, for example for the date May, 2nd the minimum time is 10:30 and the maximum time is 15:45, any time before 10:30 or after 15:45 at may 2nd will be resulting "NO GROUP"

 

[scorpidxls]

how about
=IF(D2>MAX(FILTER($A$2:$A$6,INT($A$2:$A$6)=INT(D2))),"not in group", VLOOKUP(D2,$A$2:$B$6,2))

Book6
	A	B	C	D	E	F	G
1				Date	group - wrong result		Filter group correct result
2	5/2/24 10:30	1		5/2/24 18:45	6		not in group
3	5/2/24 10:49	1
4	5/2/24 15:45	6		5/2/24 12:15	1		1
5	5/3/24 11:09	10
6	5/3/24 18:10	13		5/3/24 19:34	13		not in group
7
8				5/3/24 17:00	10		10
Sheet1

Cell Formulas
Range	Formula
E2,E8,E6,E4	E2	=VLOOKUP(D2,$A$2:$B$6,2)
G2,G8,G6,G4	G2	=IF(D2>MAX(FILTER($A$2:$A$6,INT($A$2:$A$6)=INT(D2))),"not in group", VLOOKUP(D2,$A$2:$B$6,2))

ahhhh THANKS ALOT !!! that formula works really well...I only adding 1 more condition for the time before, so the formula become like this :
=IF(D4>MAX(FILTER($A$2:$A$19;INT($A$2:$A$19)=INT(D4)));"not in group";IF(D4<MIN(FILTER($A$2:$A$19;INT($A$2:$A$19)=INT(D4)));"not in group";VLOOKUP(D4;$A:$B;2)))

 

[etaf]

=IF(D4>MAX(FILTER($A$2:$A$19;INT($A$2:$A$19)=INT(D4)));"not in group";IF(D4<MIN(FILTER($A$2:$A$19;INT($A$2:$A$19)=INT(D4)));"not in group";VLOOKUP(D4;$A:$B;2)))

you can use OR() , to make the formula a little smaller - not that if matters that much

HOWEVER , here it is

=IF( OR ( D4>MAX(FILTER($A$2:$A$19;INT($A$2:$A$19)=INT(D4))) ; D4<MIN(FILTER($A$2:$A$19;INT($A$2:$A$19)=INT(D4))) ) ;"not in group";VLOOKUP(D4;$A:$B;2)))

i dont use the ; - so not checked

Like Quote Reply

Like Quote Reply