Search in Excell

[blazblaz]

I have 4 column table, (A,B,C,D) where I have a D column values already determined based on A,B,C values.
Now, later in other application I want to know the D value based on some given values for A,B,C ( D=Function(A,B,C) )
For example I give a query for D=F(A=2.5, B<=1.5, C>=1.7)
If value in coulumn A Equal=2.5
If value in coulumn B Equal<=1.5
If value in coulumn C Equal>=1.7

Then value I am looking for is=D

How can I solve this problem (Just through VB programming?)
Any suggestions?

 

[Zathras]

If I am understanding you correctly, you have an ideal candidate for using database techniques.

Not sure on a couple of points, however:

1. What is the &quot;other application&quot; you mentioned? Another spreadsheet?

2. What is the value of &quot;D&quot; you are expecting if more than one line of the table meets all three criteria? The sum of all the D-values that match? Or is the database such that that can never happen?

Assuming another spreadsheet for (1) and the sum of D-values for (2), here is how it can work:

(If my assumptions are wrong, you can stop reading here, but please post again with more specifics...)

Given that your data is in a workbook named &quot;NotePad.xls&quot; and looks like this:

HEIGHT WIDTH  DEPTH  VOLUME
1  	0.1	1  	0.1
1.5	0.2	1.1	0.33
2  	0.3	1.2	0.72
2.5	0.4	1.3	1.3
3  	0.5	1.4	2.1
3.5	0.6	1.5	3.15
1  	0.7	1.6	1.12
1.5	0.8	1.7	2.04
2  	0.9	1.8	3.24
2.5	1  	1.9	4.75
3  	1.1	2  	6.6
3.5	1.2	2.1	8.82
1  	1.3	2.2	2.86
1.5	1.4	2.3	4.83
2  	1.5	2.4	7.2
2.5	1.6	2.5	10

Where the &quot;VOLUME&quot; column actually contains formulas.
Assign a range name &quot;Database&quot; to the table (4 columns x however many rows there are).

Then in another worksheet, you enter your criteria:

A1: HEIGHT
B1: WIDTH
C1: DEPTH
A2: '=2.5
B2: '<=1.5
C2: '>=1.7

Define a range &quot;Criteria&quot; for the range A1:C2

Enter in the following in the same worksheet (could go anywhere):

A5: 'Total volume:
B5: =DSUM(NotePad.xls!Database,4,Criteria)

The result in B5 should show as 4.75.
Change the criteria formulas as needed to see different results.

Lookup database formulas and database techniques in the help file to learn more about it.

 

[blazblaz]

I want to find the value A1 in worksheet #1. This value will be found in
worksheet # 2, which has column A,B,C (some 500 records, already preentered values)

WORKSHEET # 2
A B C
1. 2.5 0.5 3
2. 2.5 0.7 4
3. 2.5 1.2 5
4. 3.0 0.8 4
5. 3.0 0.9 5
6. 3.0 1.0 6
7. 3.0 1.3 7
8. 4.0 1.0 6
9. 4.0 2.0 7
10. 4.0 2.5 8

The value A1 is the function of A1= C[Func(A,B)] from worksheet #2. Example:
Criteria is to find the first record C in worksheet #2 which will satisfy A>=2.7, and B<=1.1.
First A>2.7 or A=2.7 is record # 4. (The rest of records satisfy also this conditon) Second condition first B<1.1 or B=1.1 is record is record # 6. (record # 4,5 also satisfies this requirements, but the first is rec # 6)
So, the record # 6 is the first record wich will satsify A>=2.7 and B<=1.1. For this record value C=6.
This value is what has to appear in A1 in worksheet # 1.

How to solve this problem?

 

[Zathras]

I see you are getting help from another post. Just as well. I can't see the connection between your original question with four columns and your followup with three columns. I don't think I can help you.