Hi there

I am trying to return a number to a table of information that depends on the
values of two variables x and y.
The variables may take a number of different values (the table I need to
search within for the value I need to return is is 11 rows by 19 columns).
So for a specific x and y I get a certain number back.
I have tried using OFFSET and LOOKUP functions but cant get anywhere.
Please help!
Regards Matt

Without knowing more about your data structure, you could use the SUMPRODUCT..

Say you have values of somex, and somey, and you need somevalue, and x and y
are in columns a & b, and value is in c...

=SUMPRODUCT(--($A$1:$A11=somex),--($B$1:$B$11=somey),($C$1:$C$11))

Hope this helps. If it doesn't, please give more details as to your data
structure, if your x and y column lookup is also variable, etc.
--
John C


"Matt G" wrote:

Hi there

I am trying to return a number to a table of information that depends on the
values of two variables x and y.
The variables may take a number of different values (the table I need to
search within for the value I need to return is is 11 rows by 19 columns).
So for a specific x and y I get a certain number back.
I have tried using OFFSET and LOOKUP functions but cant get anywhere.
Please help!
Regards Matt

Hi John

I should've stated that the values x and y are also variable. A sample of
the table is as follows: x runs across colums and y runs from top to bottom
row.

HVL(mm)(x)
T(cm)(y) 0.3 0.31 0.32 0.33 0.34
2 0.390 0.395 0.401 0.412 0.422
3 0.274 0.278 0.283 0.292 0.300
4 0.207 0.211 0.214 0.221 0.228
4.5 0.183 0.186 0.189 0.196 0.202
5 0.164 0.167 0.170 0.176 0.181
6 0.135 0.137 0.140 0.145 0.149
7 0.114 0.116 0.118 0.122 0.126

So certain values of HVL (x) and T(y) occuring point to a certain value in
the table. It's this value I need to return to another table.

Thanks for your time.
Matt
"John C" wrote:

Without knowing more about your data structure, you could use the SUMPRODUCT..

Say you have values of somex, and somey, and you need somevalue, and x and y
are in columns a & b, and value is in c...

=SUMPRODUCT(--($A$1:$A11=somex),--($B$1:$B$11=somey),($C$1:$C$11))

Hope this helps. If it doesn't, please give more details as to your data
structure, if your x and y column lookup is also variable, etc.
--
John C


"Matt G" wrote:

Hi there

I am trying to return a number to a table of information that depends on the
values of two variables x and y.
The variables may take a number of different values (the table I need to
search within for the value I need to return is is 11 rows by 19 columns).
So for a specific x and y I get a certain number back.
I have tried using OFFSET and LOOKUP functions but cant get anywhere.
Please help!
Regards Matt

Assuming your table starts in row 1, column A....

Try the following formula:
=VLOOKUP(xvalue,$A$1:$F$9,INDEX(MATCH(yvalue,$A$2: $F$2,1),1),FALSE)

Note, this also assumes your Y values are in row 2, from A through F.

Hope this helps.

--
John C


"Matt G" wrote:

Hi John

I should've stated that the values x and y are also variable. A sample of
the table is as follows: x runs across colums and y runs from top to bottom
row.

HVL(mm)(x)
T(cm)(y) 0.3 0.31 0.32 0.33 0.34
2 0.390 0.395 0.401 0.412 0.422
3 0.274 0.278 0.283 0.292 0.300
4 0.207 0.211 0.214 0.221 0.228
4.5 0.183 0.186 0.189 0.196 0.202
5 0.164 0.167 0.170 0.176 0.181
6 0.135 0.137 0.140 0.145 0.149
7 0.114 0.116 0.118 0.122 0.126

So certain values of HVL (x) and T(y) occuring point to a certain value in
the table. It's this value I need to return to another table.

Thanks for your time.
Matt
"John C" wrote:

Without knowing more about your data structure, you could use the SUMPRODUCT..

Say you have values of somex, and somey, and you need somevalue, and x and y
are in columns a & b, and value is in c...

=SUMPRODUCT(--($A$1:$A11=somex),--($B$1:$B$11=somey),($C$1:$C$11))

Hope this helps. If it doesn't, please give more details as to your data
structure, if your x and y column lookup is also variable, etc.
--
John C


"Matt G" wrote:

Hi there

I am trying to return a number to a table of information that depends on the
values of two variables x and y.
The variables may take a number of different values (the table I need to
search within for the value I need to return is is 11 rows by 19 columns).
So for a specific x and y I get a certain number back.
I have tried using OFFSET and LOOKUP functions but cant get anywhere.
Please help!
Regards Matt

An index/match could also be used ..

Assume your posted table is within A1:F9,
data in B3:F9, row headers (x) in B2:F2, col headers (y) in A3:A9

Assume you have the paired (y, x) inputs in H2:I2 down,
eg in H2: 4.5, in I2: 0.32

then you could place this in J2:
=INDEX($B$3:$F$9,MATCH(H2,$A$3:$A$9,0),MATCH(I2,$B $2:$F$2,0))
to return the intersection data, viz: 0.189

Copy J2 down to return correspondingly for other paired inputs in H3:I3,
H4:I4, etc
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Matt G" wrote:
I should've stated that the values x and y are also variable. A sample of
the table is as follows: x runs across colums and y runs from top to bottom
row.

HVL(mm)(x)
T(cm)(y) 0.3 0.31 0.32 0.33 0.34
2 0.390 0.395 0.401 0.412 0.422
3 0.274 0.278 0.283 0.292 0.300
4 0.207 0.211 0.214 0.221 0.228
4.5 0.183 0.186 0.189 0.196 0.202
5 0.164 0.167 0.170 0.176 0.181
6 0.135 0.137 0.140 0.145 0.149
7 0.114 0.116 0.118 0.122 0.126

So certain values of HVL (x) and T(y) occuring point to a certain value in
the table. It's this value I need to return to another table.

Hi Max

This appears to have worked.

Thanks a lot for getting back.

Matt

"Max" wrote:

An index/match could also be used ..

Assume your posted table is within A1:F9,
data in B3:F9, row headers (x) in B2:F2, col headers (y) in A3:A9

Assume you have the paired (y, x) inputs in H2:I2 down,
eg in H2: 4.5, in I2: 0.32

then you could place this in J2:
=INDEX($B$3:$F$9,MATCH(H2,$A$3:$A$9,0),MATCH(I2,$B $2:$F$2,0))
to return the intersection data, viz: 0.189

Copy J2 down to return correspondingly for other paired inputs in H3:I3,
H4:I4, etc
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Matt G" wrote:
I should've stated that the values x and y are also variable. A sample of
the table is as follows: x runs across colums and y runs from top to bottom
row.

HVL(mm)(x)
T(cm)(y) 0.3 0.31 0.32 0.33 0.34
2 0.390 0.395 0.401 0.412 0.422
3 0.274 0.278 0.283 0.292 0.300
4 0.207 0.211 0.214 0.221 0.228
4.5 0.183 0.186 0.189 0.196 0.202
5 0.164 0.167 0.170 0.176 0.181
6 0.135 0.137 0.140 0.145 0.149
7 0.114 0.116 0.118 0.122 0.126

So certain values of HVL (x) and T(y) occuring point to a certain value in
the table. It's this value I need to return to another table.

Welcome, glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"Matt G" wrote in message
...
Hi Max

This appears to have worked.
Thanks a lot for getting back.

Matt