Database Analysis Question

S

skier464

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!
 
M

Max

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.
 
A

Alan Beban

skier464 said:
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!
Click to expand...
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
 
Top