Interogating Tables

[benreade]

Hi All

I'm hoping someone can help with a fuinction I need in excel, which
believe would be quite simple, but I can't suss it out.

I have a table with row and column values containing cost data. give
a particular combination of x and y I need excell to pick th
appropriate value. Almost like reading a map, eg. tell me whats a
grid reference xxx yyy (a pub!)

I have two VBA combi box's so the user can select each of these values
all I need now is a function that returns the cost. hopefully simple

 

[AlfD]

Hi!

One way:

=VLOOKUP(A1,C1:Z100,B1,False)

where A1 is the value (not row number) being looked-up in the firs
column of table C1:Z100, looking in the B1'th column of that table.

Any use?

Al

 

[Max]

Assuming you have in Sheet1, in A14, the table

..... y1 y2 y3
x1 24 27 82
x2 65 56 11
x3 35 59 50

In Sheet2
-----------

Suppose A1 and B1 will contain the selections made for x and y
(Example: in A1: x1 and in B1: y2)

Put in say, C1:

=OFFSET(Sheet1!$A$1,MATCH($A1,Sheet1!$A:$A,0)-1,MATCH($B1,Sheet1!$1:$1,0)-1)

C1 will return the intersection value from the table in Sheet1
for the x and y values selected(or input) in A1 and B1

If, in A1: x1 and in B1: y2
C1 will return 27