Hello everyone

Heres my delima,

I want to lookup information from diffrent tables ie...

I want to fill in information in mutiple cells and have 2 cells determin which table to look in and then have 2 cells determine which row and column index to find the interseting cells information and then place that data in a cell in my spread sheet. thus allowing a math formula to be calculated based on the criterior of the 4 cells.


example in the first cell i want to enter "I", second cell "3" this will look in table I3. in the third cell I want to enter "30", in the 4th cell "460" these will be the row and column headers. the interceting cell data is what I want in entered into my spread sheet that will allow a math function to be completed.

Can anyone help me?

Let's say:

A1 = I
A2 = 3
A4 = 30
A5 = 460
A6 = Lookup value

A7=VLOOKUP(A6,OFFSET(INDIRECT(CONCATENATE(A1,A2)), 0,0,A4,A5),2,0)

Explanation for the OFFSET function:
=OFFSET(Reference, Rows, Columns, [Height],[Width])

Reference: INDIRECT(CONCATENATE(A1,A2))
This is your starting point for the table (I3).

Row: 0
This is to tell the function how many times to go down starting from the Reference.

Columns: 0
This is to tell the function how many times to go to the right starting from the Reference.

Height: A4
This tell the function how many rows does the table have (30)

Width: A5
This tell the function how many columns does the table have (460)

I am inputing a value into cell "F4" that value is "30"
I am inputing a value into cell "G4" that value is "I"
I am inputing a value into cell "I4" that value is "3"
I am inputing a value into cell "K4" that value is "460"

the above values will be manualy inputed into a spread sheet.

I want the formula in cell "L4" so that the data is looked up and displayed in cell "L4".

This is the table.....
I want the formula to look in column "AA" and find "30" and in row "3" and find "460 then find the intersection of the approiate column and row and return that value to cell "L4". remember the values listed above will chanke based on the criterior of the user. and the formula will look in this table and find the data they desire.

AA AB AC AD AE
induction
HP I3
115 230 460 2300
0.5 4.4 2.2 1.1 --
0.75 6.4 3.2 1.6 --
1 8.4 4.2 2.1 --
1.5 12 6 3 --
2 13.6 6.8 3.4 --
3 -- 9.6 4.8 --
5 -- 15.2 7.6 --
7.5 -- 22 11 --
10 -- 28 14 --
15 -- 42 21 --
20 -- 54 27 --
25 -- 68 34 --
30 -- 80 40 --
40 -- 104 52 --
50 -- 130 65 --
60 -- 154 77 16
75 -- 192 96 20
100 -- 248 124 26
125 -- 312 156 31
150 -- 360 180 37
200 -- 480 240 49
250 -- -- 302 60
300 -- -- 361 72
350 -- -- 414 83
400 -- -- 477 95
450 -- -- 515 103
500 -- -- 590 118



can you help?

here's more info to help clear things up.

I am inputing a value into cell "F4" that value is "30"
I am inputing a value into cell "G4" that value is "I"
I am inputing a value into cell "I4" that value is "3"
I am inputing a value into cell "K4" that value is "460"

these values will change based on the user and what they are looking for.

the above values will be manualy inputed into a spread sheet.

I want the formula in cell "L4" so that the data is looked up and displayed in cell "L4".

This is the table.....
I want the formula to look in column "AA" and find "30" and in row "3" and find "460 then find the intersection of the approiate column and row and return that value to cell "L4". remember the values listed above will chanke based on the criterior of the user. and the formula will look in this table and find the data they desire.

AA AB AC AD AE
induction
HP I3
115 230 460 2300
0.5 4.4 2.2 1.1 --
0.75 6.4 3.2 1.6 --
1 8.4 4.2 2.1 --
1.5 12 6 3 --
2 13.6 6.8 3.4 --
3 -- 9.6 4.8 --
5 -- 15.2 7.6 --
7.5 -- 22 11 --
10 -- 28 14 --
15 -- 42 21 --
20 -- 54 27 --
25 -- 68 34 --
30 -- 80 40 --
40 -- 104 52 --
50 -- 130 65 --
60 -- 154 77 16
75 -- 192 96 20
100 -- 248 124 26
125 -- 312 156 31
150 -- 360 180 37
200 -- 480 240 49
250 -- -- 302 60
300 -- -- 361 72
350 -- -- 414 83
400 -- -- 477 95
450 -- -- 515 103
500 -- -- 590 118



can you help?

=OFFSET(AA3,MATCH(F4,$AA:$AA,0)-1,MATCH(K4,$3:$3,0)-1,1,1)