Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Numeric Value to their Matched Value Position in Single Column
Hi All,
I have a dynamic named range "Data" spanning 8 columns and many rows. "Data" houses numeric values. I am using the following Formula to return 8 cells from my 18th row of "Data" to a single column. The row of numeric values in "Data" are in ascending order. =INDEX(Data,ROWS(Data)-17,ROWS($1:1)) However, I would like a single Formula to return the (eight cell) results from my 18th row of "Data" to a row in a single column that matches the value of the result. Using the above Formula - Expected Results from 18th row of "Data" (values will vary): 45, 50, 57, 60, 72, 79, 84, 85 The above values should be returned to their corresponding value Position in a single column that spans column & row N17 to N103. Numeric Value 45 should be returned to Position 45 in my column range N17: N103 = Row N61 Numeric Value 50 should be returned to Position 50 in my column range N17: N103 = Row N66 Numeric Value 57 should be returned to Position 57 in my column range N17: N103 = Row N73 Numeric Value 60 should be returned to Position 60 in my column range N17: N103 = Row N76 Numeric Value 72 should be returned to Position 72 in my column range N17: N103 = Row N88 Numeric Value 79 should be returned to Position 79 in my column range N17: N103 = Row N95 Numeric Value 84 should be returned to Position 84 in my column range N17: N103 = Row N100 Numeric Value 85 should be returned to Position 85 in my column range N17: N103 = Row N101 Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200703/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Numeric Value to their Matched Value Position in Single Column
I have a dynamic named range "Data" spanning 8 columns and many rows. "Data" houses numeric values. I am using the following Formula to return 8 cells from my 18th row of "Data" to a single column. The row of numeric values in "Data" are in ascending order. =INDEX(Data,ROWS(Data)-17,ROWS($1:1)) However, I would like a single Formula to return the (eight cell) results from my 18th row of "Data" to a row in a single column that matches the value of the result. To get all 8 cells in a contiguous 1x8 range: Select all 8 cells and enter: =INDEX(Data,18,0) This is an array formula, hence commit with Shift+Ctrl+Enter. Using the above Formula - Expected Results from 18th row of "Data" (values will vary): 45, 50, 57, 60, 72, 79, 84, 85 The above values should be returned to their corresponding value Position in a single column that spans column & row N17 to N103. Numeric Value 45 should be returned to Position 45 in my column range N17: N103 = Row N61 Let us say these values were collected in cells E1:L1. In N17 enter the following formula and copy through N103: =IF(ISNUMBER(MATCH(ROW()-ROW($N$17)+1,$E$1:$L$1,0)),ROW()-ROW($N $17)+1,"") HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Numeric Value to their Matched Value Position in Single Column
Hi vezerid,
Thank you very much for your time and assistance. Great Input! I tweaked your Formula so that I could have a single formula to find the relevant row of numbers and return them to their corresponding row value Position in a single column. =IF(ISNUMBER(MATCH(ROW()-ROW($N$17)+1,INDEX(Data,ROWS(Data)-17,0),0)),ROW()- ROW($N$17)+1,"") I would not have got the final solution without your help! Very much appreciated. Cheers, Sam vezerid wrote: To get all 8 cells in a contiguous 1x8 range: Select all 8 cells and enter: =INDEX(Data,18,0) This is an array formula, hence commit with Shift+Ctrl+Enter. Let us say these values were collected in cells E1:L1. In N17 enter the following formula and copy through N103: =IF(ISNUMBER(MATCH(ROW()-ROW($N$17)+1,$E$1:$L$1,0)),ROW()-ROW($N $17)+1,"") HTH Kostis Vezerides -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200703/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Numeric Value to their Matched Value Position in Single Column
On Mar 6, 7:01 pm, "Sam via OfficeKB.com" <u4102@uwe wrote:
Hivezerid, Thank you very much for your time and assistance. Great Input! I tweaked your Formula so that I could have a single formula to find the relevant row of numbers and return them to their corresponding row value Position in a single column. =IF(ISNUMBER(MATCH(ROW()-ROW($N$17)+1,INDEX(Data,ROWS(Data)-17,0),0)),ROW()- ROW($N$17)+1,"") I would not have got the final solution without your help! Very much appreciated. Cheers, Sam vezeridwrote: To get all 8 cells in a contiguous 1x8 range: Select all 8 cells and enter: =INDEX(Data,18,0) This is an array formula, hence commit with Shift+Ctrl+Enter. Let us say these values were collected in cells E1:L1. In N17 enter the following formula and copy through N103: =IF(ISNUMBER(MATCH(ROW()-ROW($N$17)+1,$E$1:$L$1,0)),ROW()-ROW($N $17)+1,"") HTH Kostis Vezerides -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200703/1 Glad it worked. Thanks for the feedback. Kostis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Matched Numeric Labels across Single Row | Excel Worksheet Functions | |||
Return Matched Numeric Values across Rows | Excel Worksheet Functions | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
Return Single Row of Numeric Data to Single Column | Excel Worksheet Functions | |||
Return Single Instance of Numeric Values from a Column | Excel Worksheet Functions |