If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Index headache
I'm using the folowing formula to locate a specific cell
and return the value in that cell based on matching 2 different values to values in cells to the side of and above the specific value I want (I'm using the values in two columns as x y coordinates to locate and enter a value in a cell automatically. =INDEX(Sheet1!$B$1:$AE$100,MATCH(A31,Sheet1! $B$1:$B$100,0),MATCH(B31,Sheet1!$C$1:$AE$1,0)) Unfortunately this always give me the value one cell left of the value I want. Any ideas thanks in advance ps. the numbers used s coordinates are in columns a ,b sheet 1 has the values i need. the range of values to be matched with column b is sheet 1 c1:ae1 (x coord) The range of values to be matched with column a is sheet 1 b1:b100 (y coord) ( which seems to be working fine. |
#2
|
|||
|
|||
Index headache
Hi
personally, i'ld use the offset function =OFFSET(Sheet1!$B$1,MATCH(A31,Sheet1! $B$1:$B$100,0),MATCH(B31,Sheet1!$C$1:$AE$1,0)) but if you really want to use the index function change it to =INDEX(Sheet1!$C$2:$AE$100,MATCH(A31,Sheet1! $B$1:$B$100,0),MATCH(B31,Sheet1!$C$1:$AE$1,0)) i.e. start it in the result area not the title area let us know how you go Cheers JulieD wrote in message ... I'm using the folowing formula to locate a specific cell and return the value in that cell based on matching 2 different values to values in cells to the side of and above the specific value I want (I'm using the values in two columns as x y coordinates to locate and enter a value in a cell automatically. =INDEX(Sheet1!$B$1:$AE$100,MATCH(A31,Sheet1! $B$1:$B$100,0),MATCH(B31,Sheet1!$C$1:$AE$1,0)) Unfortunately this always give me the value one cell left of the value I want. Any ideas thanks in advance ps. the numbers used s coordinates are in columns a ,b sheet 1 has the values i need. the range of values to be matched with column b is sheet 1 c1:ae1 (x coord) The range of values to be matched with column a is sheet 1 b1:b100 (y coord) ( which seems to be working fine. |
#3
|
|||
|
|||
Index headache
Probably use
MATCH(B31,Sheet1!$B$1:$AE$1,0) instead of MATCH(B31,Sheet1!$C$1:$AE$1,0)) Replace math array of $C$1:$ae$1 to $b$1:$AE$1 as you have indexed $B1:$AE100 -----Original Message----- I'm using the folowing formula to locate a specific cell and return the value in that cell based on matching 2 different values to values in cells to the side of and above the specific value I want (I'm using the values in two columns as x y coordinates to locate and enter a value in a cell automatically. =INDEX(Sheet1!$B$1:$AE$100,MATCH(A31,Sheet1! $B$1:$B$100,0),MATCH(B31,Sheet1!$C$1:$AE$1,0)) Unfortunately this always give me the value one cell left of the value I want. Any ideas thanks in advance ps. the numbers used s coordinates are in columns a ,b sheet 1 has the values i need. the range of values to be matched with column b is sheet 1 c1:ae1 (x coord) The range of values to be matched with column a is sheet 1 b1:b100 (y coord) ( which seems to be working fine. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
find keywords | Tennessee picker | General Discussion | 1 | July 7th, 2004 06:23 AM |
How to repair index corruption | chris | Database Design | 9 | June 25th, 2004 11:40 AM |
Index and section breaks | raj | Page Layout | 0 | May 31st, 2004 10:01 AM |
Indexing - Global refresh/deletion of index marks | Ray Cleere | Formatting Long Documents | 2 | April 30th, 2004 04:31 PM |
Indexing - Global refresh/deletion of index marks | Ray Cleere | Formatting Long Documents | 0 | April 30th, 2004 10:46 AM |