I have a table where I want a value based on looking at data in two columns
as follows;
Table:
A B C
1 Bore Pressure Length
2 100 760 80
3 100 1050 100
4 100 1200 100
5 125 670 80
6 125 830 100

I want to find a value in column C, based on an exact value from column A
and when given a value for column B the value immediately above it. (e.g If
i had a bore of 100 and pressure of 900, I would want the value of 100 from
row 3)
Hope this explains it, i have tried SUMPRODUCT in various formats but have
been unsuccessful.
Thanks

Try this array formula

=INDEX(C2:C6,MATCH(1,(A2:A6=100)*(B2:B6=900),0))

--

HTH

Bob

"stretch" wrote in message
...
I have a table where I want a value based on looking at data in two columns
as follows;
Table:
A B C
1 Bore Pressure Length
2 100 760 80
3 100 1050 100
4 100 1200 100
5 125 670 80
6 125 830 100

I want to find a value in column C, based on an exact value from column A
and when given a value for column B the value immediately above it. (e.g
If
i had a bore of 100 and pressure of 900, I would want the value of 100
from
row 3)
Hope this explains it, i have tried SUMPRODUCT in various formats but have
been unsuccessful.
Thanks

I have tried this formula but it just gives a #VALUE, any ideas?




"Bob Phillips" wrote:

Try this array formula

=INDEX(C2:C6,MATCH(1,(A2:A6=100)*(B2:B6=900),0))

--

HTH

Bob

"stretch" wrote in message
...
I have a table where I want a value based on looking at data in two columns
as follows;
Table:
A B C
1 Bore Pressure Length
2 100 760 80
3 100 1050 100
4 100 1200 100
5 125 670 80
6 125 830 100

I want to find a value in column C, based on an exact value from column A
and when given a value for column B the value immediately above it. (e.g
If
i had a bore of 100 and pressure of 900, I would want the value of 100
from
row 3)
Hope this explains it, i have tried SUMPRODUCT in various formats but have
been unsuccessful.
Thanks


.

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.


stretch wrote:

I have tried this formula but it just gives a #VALUE, any ideas?

"Bob Phillips" wrote:

Try this array formula

=INDEX(C2:C6,MATCH(1,(A2:A6=100)*(B2:B6=900),0))

--

HTH

Bob

"stretch" wrote in message
...
I have a table where I want a value based on looking at data in two columns
as follows;
Table:
A B C
1 Bore Pressure Length
2 100 760 80
3 100 1050 100
4 100 1200 100
5 125 670 80
6 125 830 100

I want to find a value in column C, based on an exact value from column A
and when given a value for column B the value immediately above it. (e.g
If
i had a bore of 100 and pressure of 900, I would want the value of 100
from
row 3)
Hope this explains it, i have tried SUMPRODUCT in various formats but have
been unsuccessful.
Thanks


.


--

Dave Peterson

It works fine now, many thanks


"Dave Peterson" wrote:

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.


stretch wrote:

I have tried this formula but it just gives a #VALUE, any ideas?

"Bob Phillips" wrote:

Try this array formula

=INDEX(C2:C6,MATCH(1,(A2:A6=100)*(B2:B6=900),0))

--

HTH

Bob

"stretch" wrote in message
...
I have a table where I want a value based on looking at data in two columns
as follows;
Table:
A B C
1 Bore Pressure Length
2 100 760 80
3 100 1050 100
4 100 1200 100
5 125 670 80
6 125 830 100

I want to find a value in column C, based on an exact value from column A
and when given a value for column B the value immediately above it. (e.g
If
i had a bore of 100 and pressure of 900, I would want the value of 100
from
row 3)
Hope this explains it, i have tried SUMPRODUCT in various formats but have
been unsuccessful.
Thanks


.


--

Dave Peterson
.