Use of OFFSET and LOOKUP to find a value in a table

[Matt G]

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

 

[John C]

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.

 

[Matt G]

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]

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.

 

[Max]

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]

Hi Max

This appears to have worked.

Thanks a lot for getting back.

Matt

 

[Max]

Welcome, glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik