VLOOKUP Isssue

[sguirgies]

I'm developing a sprdeadsheet using MROUND and VLOOKUP.

The VLOOKUP acts strange when the MROUND cell displays a result of 2:30, 3:30...+ but not otherwise. it returns the result for 2:15, 3:15...+. If i override the MROUND and enter 2:30 manuallly it works. Paste special does not work.

I have checked the result of the MROUND using ISNUMBER and LEN, it is the same value as the VLOOKUP it looking for.

PLEASE HELP.

 

[etaf]

is this a TIME value you are returning from vlookup
and what is Mround expected to do

perhaps post the formula you are using

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

 

[sguirgies]

https://1drv.ms/x/c/fc25f73660be22c5/EbqccSByrD5ElZN_E2c9UTIBs0bS-aglbof1xgQRIjW2Wg?e=I9f3Nf

 

[etaf]

thanks for the spreadsheet
I have no idea what I should be looking at , no explanation of what cells you are using
can you provide an explanation and the expected results

The spreadsheet opened in column N - no formula
i see a vlookup in O
and a blank range highlighted in Q12 - Q14

=IFS(

H2="Yes",VLOOKUP(A!F2,'Pricing Main'!$A$1:$C$11,3),
I2="phone",VLOOKUP(A!F2,'Pricing Main'!$A$13:$C$28,3),
I2="video",VLOOKUP(A!F2,'Pricing Main'!$A$31:$C$48,3))

As you dont have TRUE or FALSE at the end of the vlookup - it will default to TRUE and find the nearest lower value in the column MAIN A


i have downloaded to excel and also made available here as XL2BB in case other members dont want to go to onedrive and can see whats going on and maybe help

sdd mock.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1				Time in	Time out	Billed time		Onsite	Location						$
2				14:00	15:00	1:30		Yes							10
3				15:30	16:00	0:30		No	Video						1
4				9:00	17:00	8:00		No	Video						15
5				10:45	11:15	0:30		No	Video						1
6				15:00	16:00	1:00		No	Phone						3
7				4:30 PM	5:30 PM	1:00		No	Video						3
8				9:00 AM	4:00 PM	7:00		No							#N/A
9				10:00 AM	1:00 PM	3:00		Yes							47
10				1:00 PM	2:00 PM	1:00		No	Video						3
11				1:00 PM	2:00 PM	1:30		Yes							10
12				10:00 AM	11:00 AM	1:30		Yes							10
13				11:00 AM	12:30 PM	1:30		No	Phone						5
14				10:30 AM	11:30 AM	1:30		Yes							10
15				1:30 PM	2:30 PM	1:00		No	Video						3
16				10:00 AM	11:00 AM	1:30		Yes							10
17				3:00 PM	3:30 PM	0:30		No	Video						1
18				9:30 AM	11:00 AM	1:30		Yes							10
19				11:10 AM	1:40 PM	2:30		No	Phone						8
20				11:30 AM	1:30 PM	2:00		Yes							20
21				3:00 PM	4:00 PM	1:00		No	Phone						3
22				4:30 PM	5:30 PM	1:00		No	Phone						3
23				9:00 AM	11:30 AM	2:30			Phone						8
A

Cell Formulas
Range	Formula
F2:F22	F2	=IF(H2="Yes",IF((E2-D2<3/48),3/48,MROUND((E2-D2),1/48)),MROUND(E2-D2,1/96))
O2:O23	O2	=IFS(H2="Yes",VLOOKUP(A!F2,'Pricing Main'!$A$1:$C$11,3),I2="phone",VLOOKUP(A!F2,'Pricing Main'!$A$13:$C$28,3),I2="video",VLOOKUP(A!F2,'Pricing Main'!$A$31:$C$48,3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H1,P:P,H33:H1048576	Cell Value	contains "No"	text	NO

sdd mock.xlsx
	A	B	C	D
1	Onsite
2	1:30		10
3	2:00		20
4	2:30		30
5	3:00		47
6	3:30		50
7	4:00		60
8	4:30		70
9	5:00		80
10	5:30		90
11	6:00		100
12
13	Telephone
14	0:30	30m	1
15	0:45	45m	2
16	1:00	60m	3
17	1:15	75m	4
18	1:30	90m	5
19	1:45	105m	6
20	2:00	120m	7
21	2:15	135m	8
22	2:30	150m	9
23	2:45	165m	10
24	3:00	180m	11
25	3:15	195m	12
26	3:30	210m	13
27	3:45		14
28	4:00		15
29
30
31	Video
32	0:30	30m	1
33	0:45	45m	2
34	1:00	60m	3
35	1:15	75m	4
36	1:30	90m	5
37	1:45	105m	6
38	2:00	120m	7
39	2:15		8
40	2:30	150m	9
41	2:45		10
42	3:00	180m	11
43	3:15		12
44	3:30	210m	13
45	3:45		14
46	4:00		15
47
Pricing Main

 

[sguirgies]

Apologies I didn't actually realise how convoluted is looked when I scrubbed sensitive data.

In sheet A, Column D and E, time in and out, H whether it's onsite or not, I if not onsite is it video or phone (I scrubbed addresses). column F is based on the above but essentially I want the difference between D and E. If it's onsite, I want it rounded up to the nearest half hour with a minimum of 1.5 hours, if not I want it rounded up to the nearest 15 minutes with a 0.5 hour minimum. I knoow I need to tweak that formula, including a similar nested if for a minimum of 0.5 hours where the initial if is not satisfied possibly using ROUNDUP innstead of MROUND.

Following that:
There is a IFS with VLOOKUPs as conditions in O to grab from the appropriate table in the second tab. My issue is if I alow the formula in F to calculate 2.5 hours the VLOOKUP returns the value for 2:15 in the correct table (same issue with 3:30, returns 3:15 but other vauesare fine). If I manually type 2:30 (or 3:30) it returns the value it should. Why is it breaking?

Thank you for your effort.

 

[etaf]

i think part of the issue maybe the floating point error in excel
i change to use false so that vlookup using an exact match and it failed with a lot of N/A, so a time calculation by formula - does not match an entry manually - hence the issue you are getting

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.

learn.microsoft.com

not sure at the moment on best way to fix with the time issue - other members may help here

I tested 2:30 on F19
with the value in A22
and changed to general
0.104166667000000000
and they both matched visually

BUT if i copied to E22 and did a
A22=E22 , then i get a false

 

[Peter_SSs]

A couple of preliminary comments

• Your formula is on sheet 'A' and you have used that sheet name in your formula: =IFS(H2="Yes",VLOOKUP(A!F2,'Pricing Main'!$A$1:$C$11,3),I2="phone",VLOOKUP(A!F2, .....
  That is not needed and in fact a bad idea as it can lead to unflagged errors in some instances. Example of this problem you can see/test here
  
  
• IFS is an inefficient function and I would recommend against it. Inefficient because it checks every condition and calculates every result even if the first condition turns out to be the used one. Nested IF functions, or sometimes other options are generally more efficient

I believe that the floating point issue that @etaf has raised is the issue for you. My suggestion is to name the 3 lookup tables on 'Pricing Main' and try the formula shown in col P below.
I have rounded all the time values in the formula.

sguirgies sdd mock.xlsx
	D	E	F	G	H	I						O	P
1	Time in	Time out	Billed time		Onsite	Location						Original	Suggested
2	14:00	15:00	1:30		Yes							10	10
3	15:30	16:00	0:30		No	Video						1	1
4	9:00	17:00	8:00		No	Video						15	15
5	10:45	11:15	0:30		No	Video						1	1
6	15:00	16:00	1:00		No	Phone						3	3
7	4:30 PM	5:30 PM	1:00		No	Video						3	3
8	9:00 AM	4:00 PM	7:00		No							#N/A	#N/A
9	10:00 AM	1:00 PM	3:00		Yes							47	47
10	1:00 PM	2:00 PM	1:00		No	Video						3	3
11	1:00 PM	2:00 PM	1:30		Yes							10	10
12	10:00 AM	#######	1:30		Yes							10	10
13	11:00 AM	#######	1:30		No	Phone						5	5
14	10:30 AM	#######	1:30		Yes							10	10
15	1:30 PM	2:30 PM	1:00		No	Video						3	3
16	10:00 AM	#######	1:30		Yes							10	10
17	3:00 PM	3:30 PM	0:30		No	Video						1	1
18	9:30 AM	#######	1:30		Yes							10	10
19	11:10 AM	1:40 PM	2:30		No	Phone						8	9
20	11:30 AM	1:30 PM	2:00		Yes							20	20
21	3:00 PM	4:00 PM	1:00		No	Phone						3	3
22	4:30 PM	5:30 PM	1:00		No	Phone						3	3
23	9:00 AM	11:30 AM	2:30			Phone						8	9
A

Cell Formulas
Range	Formula
O2:O23	O2	=IFS(H2="Yes",VLOOKUP(A!F2,'Pricing Main'!$A$1:$C$11,3),I2="phone",VLOOKUP(A!F2,'Pricing Main'!$A$13:$C$28,3),I2="video",VLOOKUP(A!F2,'Pricing Main'!$A$31:$C$48,3))
P2:P23	P2	=LET(tbl,IF(H2="Yes",Onsite,IF(I2="phone",Telephone,IF(I2="video",Video,NA()))),MAX(FILTER(TAKE(tbl,,-1),ROUND(TAKE(tbl,,1),8)<=ROUND(F2,8),NA())))
F2:F22	F2	=IF(H2="Yes",IF((E2-D2<3/48),3/48,MROUND((E2-D2),1/48)),MROUND(E2-D2,1/96))

Named Ranges
Name	Refers To	Cells
Onsite	='Pricing Main'!$A$2:$C$11	O2:P23
Telephone	='Pricing Main'!$A$14:$C$28	O2:P23
Video	='Pricing Main'!$A$32:$C$46	O2:P23