Excel Formula to Lookup Values Based on Criteria

[Teal]

Hello,

I have found your site through the Access forum. Here is the link. Where I have posted the same problem. I need to lookup data from a list for instance: A2:A10 based on the value of B2. The value of B2 must be the sum total of the filtered values. In other words, the sum of the lookup data must be equal to the criteria in B2. Can you help me find an Excel formula for this?

Regards,
Teal

 

[Teal]

Ok it happened because when vba calculate decimal number, it will compare exactly all the digits of decimal part so when you compare it with your criteria with round of decimal digits, it will not equals. So i think this will work:

Sub SumWithMatchCrit()
    Dim lr As Long
    Dim cll As Range
    Dim test As Range
    Dim trng As Range
    Dim rng As Range
    Dim crit As Double
    Dim tVal As Double
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range("A2:A" & lr)
    rng.Interior.Pattern = xlNone
    crit = Cells(2, 2).Value
    For Each cll In rng
        Set trng = cll
        For Each test In rng
            If Intersect(test, trng) Is Nothing Then
                tVal = test.Value + WorksheetFunction.Sum(trng)
                If Round(tVal, 1) = Round(crit, 1) Then 'change the digit to what you need
                    Debug.Print Union(trng, test)
                    Union(trng, test).Interior.Color = RGB(255, 255, 0)
                ElseIf tVal < crit Then
                    Set trng = Union(trng, test)
                End If
            End If
        Next test
    Next cll
End Sub

@eiloken Hello,

Thank you for the awesome code. I liked how you used the logic to find the criteria and Round to handle the decimal situations. I just have a few doubts to clear.

The output value, however, does not exactly match the criteria in the above mini-sheet. It is close to the criteria but I need an exact match. Am I missing something?

Also, please see the new mini-sheet watched in this reply. The code is not working in this case. Please, check the code for this data too.

Thank you again for your dedication. It is really impressive.

New data:

Amjadinsaudia_Solved (2).xlsm
	A	B
1	Filter Range	Criteria
2	1394	14874.68
3	1386.64
4	532
5	26
6	486
7	220.66
8	1062
9	400
10	126.27
11	170
12	3810
13	700
14	500
15	22
16	120
17	230
18	35.65
19	421.74
20	2129.56
21	241.5
22	234.78
23	200
24	230
25	570
26	1843.48
27	358.27
28	86.96
29	15
30	35
31	125
32	30
33	900
34	20
35	150
36	20
37	70
38	50
39	40
40	200
41	15
42	435
43	7617.39
44	17.39
45	86.96
46	150
47	100
48	430
49	215
50	60
51	60
52	280
53	1100
54	157
55	400
56	180
57	300
58	220
59	522.91
60	45
61	100
62	200
63	40
64	50
65	3300
66	100
67	250
68	250
69	150
70	400
71	1400
72	15
73	65
74	1200
75	80
76	152.17
77	150
78	930
79	1505
80	116
81	60
82	60
83	530
84	80
85	50
86	570
87	300
88	100
89	100
90	43.48
91	34.78
92	34.78
93	20
94	100
95	200
96	2500
97	1500
98	47.83
99	130.44
100	547.92
101	321.77
102	1300
103	80
104	50
105	50
106	324.35
107	80
108	50
109	60
110	290
111	115
112	17.39
113	70
114	60
115	4000
116	270
117	100
118	62.5
119	520
120	30
121	100
122	634.8
123	278.26
124	140
125	289.57
126	68
127	1400
128	1000
129	180
130	30
131	80
132	117
133	14.95
134	70
Sheet1

Hints: Here, the expected output rows will be Row no. 7-15, 20-23, 56-66. Hope it helps.

Best Regards.

 

[Teal]

The output value, however, does not exactly match the criteria in the above mini-sheet. It is close to the criteria but I need an exact match. Am I missing something?

Also, please see the new mini-sheet watched in this reply. The code is not working in this case. Please, check the code for this data too.

Hello @eiloken ,

I apologize if tagging you like this is inconvenient for you. I was wondering if you need any additional information regarding this. And please, specify if this is not doable for you at this moment. Then, I could move on and think of something else for this matter.

Thank you.

 

[eiloken]

Hello @eiloken ,

I apologize if tagging you like this is inconvenient for you. I was wondering if you need any additional information regarding this. And please, specify if this is not doable for you at this moment. Then, I could move on and think of something else for this matter.

Thank you.

Sorry, i missed your feedback. yeah, my code really had a problem, it loop for each cell from top of range so it will ignore random cell in range that has sum equals to criteria, but in large data like your, i think to find all match cells, the code will loop for factorial of cells number so may be it so hard to do it in vba

 

[Teal]

Sorry, i missed your feedback. yeah, my code really had a problem, it loop for each cell from top of range so it will ignore random cell in range that has sum equals to criteria, but in large data like your, i think to find all match cells, the code will loop for factorial of cells number so may be it so hard to do it in vba

Yes, I figured. Thank you for the specification. Best Regards.