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!

One way ..

Assume source table in Sheet1,
col headers in B1 across, row headers in A2 down

In a new sheet,

In A1: 33
In B1: X

Then in C1:
=OFFSET(Sheet1!$A$1,MATCH(A1,Sheet1!A:A,0)-1,MATCH(B1,Sheet1!$1:$1,0)-1)
will return the required result from the source table (ie 4K). C1 can be
copied down to return correspondingly for other pairs of inputs in A2:B2,
A3:B3, etc.
--
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!

One way ..

Assume source table in Sheet1,
col headers in B1 across, row headers in A2 down

In a new sheet,

In A1: 33
In B1: X

Then in C1:
=OFFSET(Sheet1!$A$1,MATCH(A1,Sheet1!A:A,0)-1,MATCH(B1,Sheet1!$1:$1,0)-1)
will return the required result from the source table (ie 4K). C1 can be
copied down to return correspondingly for other pairs of inputs in A2:B2,
A3:B3, etc.
--
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!

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!
If instead of Z Y X W V U... (although those particular letters work)
and 22 33 44...(the numbers don't) you had permissible range names for
column and row headers, e.g., prod1 prod2 prod3... and item1 item2
item3, then you could highlight your table including the headers, click
Insert|Name|Create, and highlight Top row and Left column. Then you
could simply enter, e.g., =item2 prod3

That would return the sought value at the intersection.

Alan Beban

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!
If instead of Z Y X W V U... (although those particular letters work)
and 22 33 44...(the numbers don't) you had permissible range names for
column and row headers, e.g., prod1 prod2 prod3... and item1 item2
item3, then you could highlight your table including the headers, click
Insert|Name|Create, and highlight Top row and Left column. Then you
could simply enter, e.g., =item2 prod3

That would return the sought value at the intersection.

Alan Beban

Hi

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

--
Regards

Roger Govier


"skier464" <(E-Mail Removed)> wrote in message
news1923A2F-704B-466D-B541-(E-Mail Removed)...
> 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!

Hi

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

--
Regards

Roger Govier


"skier464" <(E-Mail Removed)> wrote in message
news1923A2F-704B-466D-B541-(E-Mail Removed)...
> 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!