Database Analysis Question
 

Hi,

I have the following

Column A B C D E F G
Row
1 Z Y X W V U
2 22 1A 2B 3C 4B 5A 6Z
3 33 1H 3H 4K 7B 8I 9L
4 44 7Q 7N 5M 3P 4G 3E

What I need is if ROW 1 = X and Column A = 33 then the answer is 4K. This is
for a spreadsheet that is 200 columns by 25,000 rows so I really appreciate
the help.

Thanks!

Database Analysis Question
 

Hi

Try
=INDEX($A$1:$G$4,MATCH(33,$A$1:$A$4,0),MATCH("X",$ A$1:$G$1,0))

--
Regards

Roger Govier


"skier464" wrote in message
...
Hi,

I have the following

Column A B C D E F G
Row
1 Z Y X W V U
2 22 1A 2B 3C 4B 5A 6Z
3 33 1H 3H 4K 7B 8I 9L
4 44 7Q 7N 5M 3P 4G 3E

What I need is if ROW 1 = X and Column A = 33 then the answer is 4K.
This is
for a spreadsheet that is 200 columns by 25,000 rows so I really
appreciate
the help.

Thanks!

Database Analysis Question
 

Ahh, you really shouldn't multi-post. See another option using OFFSET (albeit
volatile, it requires only a single reference cell to anchor the source
table) in your multi-post in .worksheet.functions.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"skier464" wrote:
Hi,

I have the following

Column A B C D E F G
Row
1 Z Y X W V U
2 22 1A 2B 3C 4B 5A 6Z
3 33 1H 3H 4K 7B 8I 9L
4 44 7Q 7N 5M 3P 4G 3E

What I need is if ROW 1 = X and Column A = 33 then the answer is 4K. This is
for a spreadsheet that is 200 columns by 25,000 rows so I really appreciate
the help.

Thanks!

Database Analysis Question
 

It worked fine for the example I gave, but it didn't work in the my large
spreadsheet. I have rows labeled 1 - 25000, and columns labeled 1 - 150, and
am trying to call and possible row column combo. I.e. Row 4500, column 45
gives a value at there intersection.

"Roger Govier" wrote:

Hi

Try
=INDEX($A$1:$G$4,MATCH(33,$A$1:$A$4,0),MATCH("X",$ A$1:$G$1,0))

--
Regards

Roger Govier


"skier464" wrote in message
...
Hi,

I have the following

Column A B C D E F G
Row
1 Z Y X W V U
2 22 1A 2B 3C 4B 5A 6Z
3 33 1H 3H 4K 7B 8I 9L
4 44 7Q 7N 5M 3P 4G 3E

What I need is if ROW 1 = X and Column A = 33 then the answer is 4K.
This is
for a spreadsheet that is 200 columns by 25,000 rows so I really
appreciate
the help.

Thanks!

Database Analysis Question
 

Didn't seem to work, it returned a value but not the right value
.. Maybe I had an error in the formula? Here is the formula I used
=OFFSET('data'!$A$1, MATCH(Sheet1!B15, 'data'!$1:$1, 0)-1, MATCH(Sheet1!B5,
'data'!A:A,0)-1)


'data' being my main spread sheet, Sheet1 being the secondary sheet. B15
being the column variable, and B5 being the row variable. Also I get ref when
I change the row variable much above 100.


"Max" wrote:

Ahh, you really shouldn't multi-post. See another option using OFFSET (albeit
volatile, it requires only a single reference cell to anchor the source
table) in your multi-post in .worksheet.functions.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"skier464" wrote:
Hi,

I have the following

Column A B C D E F G
Row
1 Z Y X W V U
2 22 1A 2B 3C 4B 5A 6Z
3 33 1H 3H 4K 7B 8I 9L
4 44 7Q 7N 5M 3P 4G 3E

What I need is if ROW 1 = X and Column A = 33 then the answer is 4K. This is
for a spreadsheet that is 200 columns by 25,000 rows so I really appreciate
the help.

Thanks!

Database Analysis Question
 

Think you adapted it wrong ..
.. Here is the formula I used
=OFFSET('data'!$A$1, MATCH(Sheet1!B15, 'data'!$1:$1, 0)-1, MATCH(Sheet1!B5, 'data'!A:A,0)-1)

Try this adaptation instead:
=OFFSET(data!$A$1,MATCH(Sheet1!B5,data!A:A,0)-1,MATCH(Sheet1!B15,data!$1:$1,0)-1)

where Sheet1's B5 houses the row header numbers: 22, 33, 44...
and Sheet1's B15 houses the col header letters: Z, Y, X ...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"skier464" wrote:
Didn't seem to work, it returned a value but not the right value
. Maybe I had an error in the formula? Here is the formula I used
=OFFSET('data'!$A$1, MATCH(Sheet1!B15, 'data'!$1:$1, 0)-1, MATCH(Sheet1!B5,
'data'!A:A,0)-1)


'data' being my main spread sheet, Sheet1 being the secondary sheet. B15
being the column variable, and B5 being the row variable. Also I get ref when
I change the row variable much above 100.

Database Analysis Question
 

Thanks that seemed to have solved the problem

"Max" wrote:

Think you adapted it wrong ..
.. Here is the formula I used
=OFFSET('data'!$A$1, MATCH(Sheet1!B15, 'data'!$1:$1, 0)-1, MATCH(Sheet1!B5, 'data'!A:A,0)-1)

Try this adaptation instead:
=OFFSET(data!$A$1,MATCH(Sheet1!B5,data!A:A,0)-1,MATCH(Sheet1!B15,data!$1:$1,0)-1)

where Sheet1's B5 houses the row header numbers: 22, 33, 44...
and Sheet1's B15 houses the col header letters: Z, Y, X ...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"skier464" wrote:
Didn't seem to work, it returned a value but not the right value
. Maybe I had an error in the formula? Here is the formula I used
=OFFSET('data'!$A$1, MATCH(Sheet1!B15, 'data'!$1:$1, 0)-1, MATCH(Sheet1!B5,
'data'!A:A,0)-1)


'data' being my main spread sheet, Sheet1 being the secondary sheet. B15
being the column variable, and B5 being the row variable. Also I get ref when
I change the row variable much above 100.

Database Analysis Question
 

Hi

Then just extend the ranges to

=INDEX($A$1:$ET$25000,MATCH(33,$A$1:$A$25000,0),MA TCH("X",$A$1:$ET$1,0))


--
Regards

Roger Govier


"skier464" wrote in message
...
It worked fine for the example I gave, but it didn't work in the my
large
spreadsheet. I have rows labeled 1 - 25000, and columns labeled 1 -
150, and
am trying to call and possible row column combo. I.e. Row 4500, column
45
gives a value at there intersection.

"Roger Govier" wrote:

Hi

Try
=INDEX($A$1:$G$4,MATCH(33,$A$1:$A$4,0),MATCH("X",$ A$1:$G$1,0))

--
Regards

Roger Govier


"skier464" wrote in message
...
Hi,

I have the following

Column A B C D E F G
Row
1 Z Y X W V U
2 22 1A 2B 3C 4B 5A 6Z
3 33 1H 3H 4K 7B 8I 9L
4 44 7Q 7N 5M 3P 4G 3E

What I need is if ROW 1 = X and Column A = 33 then the answer is
4K.
This is
for a spreadsheet that is 200 columns by 25,000 rows so I really
appreciate
the help.

Thanks!

Database Analysis Question
 

You're welcome !
Glad you got it working ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"skier464" wrote:
Thanks that seemed to have solved the problem