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

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

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

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