Vlookuping matching values with hidden rounding errors

[starvsnr]

I am working on a spreadsheet where a user will input data into 3 tabs and the sheet will combine this data in the desired order for the format on the Worksheet tab. I came up with a formula (I call it the Spine) that numbers each line submitted on each tab to give it its order from top to bottom when its combined on the Worksheet tab.

Below is a screenshot of my regular formula worksheet and the formula in A12

=IF(COUNTIF('Manual Carrier Responses'!A:A,A11+0.001)=1,A11+0.001,ROUND(IF(MOD(A11,1)=0,A11+0.001,IF(COUNTIF('FF Data'!A:A,A11+0.001)=1,A11+0.001,ROUNDDOWN(A11,0)+1)),3))

So the problem here is that Vlookup in column C that is using A12 as a lookup value is returning an error (iferror has it as blank). The formula in E12 is verifying that A12 matches the "1.008" cell that exists on the tab from which its looking up. If this was working correctly it would be displaying up to carrier 10 on both lists.

Playing around with this I have noticed a few behaviors:

• First if I were to overwrite the formula in A12 with the number "1.008" the lookup in C12 works but it also fixes the lookup in C13 as well but only goes that far.
• If I round the formula for my Spine with the below adjustment I get a different result, closer to complete but still issues.

  =ROUND(IF(COUNTIF('Manual Carrier Responses'!A:A,A11+0.001)=1,A11+0.001,ROUND(IF(MOD(A11,1)=0,A11+0.001,IF(COUNTIF('FF Data'!A:A,A11+0.001)=1,A11+0.001,ROUNDDOWN(A11,0)+1)),3)),3)

  

The conclusions I have come to is the fact that writing over "1.008" fixes more than one broken vlookup means there is something about my spine formula adding .001 from the number above it isn't always adding .001 every time. Even though when I do a simple this = that formula tells me its TRUE. Adding round to my spine formula changing the results seems to reinforce this theory, and the ones that get broken when I round are apparent numbers that didn't need it to begin with.

So the problem I am left with is I am trying to correct this within the spine formula. I cannot reference any broken vlookups to decide where to round the spine and when to not to, it would be circular referencing. The whole point of my spine is to vlookup off of that value.

I am hoping this is one of the more complicated vlookup questions posted on this forum. If any more context is needed let me know.

And yes all of these columns are formatted the same!

 

[awoohaw]

Are you using the ROUND() function on both sides of your condition test?

It would be a big help to the forum if you would post mini worksheets using the xl2bb add in (link below). If you can't use that then you can post a sanitized workbook on a reputable sharing site like DROPBOX or use your OneDrive.

Thanks in advance.

 

[StephenCrump]

It will be a floating-point arithmetic issue which, as @awoohaw says, can be fixed with appropriate use of ROUND().

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

	A	B
1	Compare
2	1.007	Carrier 6
3	1.008	#N/A
4	1.009	#N/A
5	1.010	#N/A
6	1.011	#N/A
7
8	To ...
9	1.007	Carrier 6
10	1.008	Carrier 7
11	1.009	Carrier 8
12	1.010	Carrier 9
13	1.011	Carrier 10
Sheet1

Cell Formulas
Range	Formula
B9:B13,B2:B6	B2	=VLOOKUP(A2,'Manual Carrier Responses'!A$1:B$12,2,)
A3:A6	A3	=A2+0.001
A10:A13	A10	=ROUND(A9+0.001,3)

	A	B
1
2
3	1.008	Carrier 7
4
5	1.009	Carrier 8
6
7	1.012	Carrier 11
8	1.007	Carrier 6
9	1.010	Carrier 9
10
11
12	1.011	Carrier 10
13
Manual Carrier Responses

 

[starvsnr]

Thank you both for the information. A quick test with rounding both ends did not work for me but I will play around with it a little more. Or I might try the precision method mentioned in the article.

I did make a test sheet and just changed the references from being decimal points to whole numbers in the thousands (1001, 1002, 1003, and so on) and that fixed the issue as well. So I will just figure out the best way to do this from here, thanks again.

 

[starvsnr]

Scratch that, rounding on both ends has worked perfectly, thanks again!

 

[awoohaw]

Happy to help. just be aware that round when used too soon in formulas can cause errors as well.
For instance ROUND(2/3,2)*10000 is not equal to Round(10000*2/3,2)

Book1
	C	D	E
1
2	2
3	3
4	10000	6700.000	6666.670
Sheet1

Cell Formulas
Range	Formula
D4	D4	=ROUND(C2/C3,2)*C4
E4	E4	=ROUND(C4*C2/3,2)

Best Wishes!