Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Database Analysis Question
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Database Analysis Question
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Database Analysis Question
Hi
=INDEX($1:$25000,MATCH(33,$A:$A,0),MATCH("X",$1:$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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ridirect & Refresh data from access database | Excel Discussion (Misc queries) | |||
Analysis Toolpak Question | Excel Discussion (Misc queries) | |||
Anybody Help with previous question | Excel Discussion (Misc queries) | |||
Users database question | Excel Discussion (Misc queries) | |||
Database Functions - question using formulas as criteria | Excel Worksheet Functions |