Re: 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

.

Relevant Pages

  • Re: Change Default Setting-Find & Replace-How?
    ... If all the occurrences are in a single row or a single column, ... Even find next has to look through the 100 cells. ... Excel tries to help by remembering the last settings you used--except ...
    (microsoft.public.excel.misc)
  • Array formula
    ... It's on the page "Eliminating Blank Cells From Lists" and the example ... I've been able to get it to work on a single column range as per the ... It successfully deals with the first row as a single unit, ...
    (microsoft.public.excel.programming)
  • Re: Breaking out data in a cell
    ... return text strings, so to convert to numbers you need to use a trick like ... I download a lot of stats via web queries. ... so cells in a single column with random data like "74th of 9354" without ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Rearranging and Indexing a column array
    ... The first column ABCD is the first line and all cells in it get the index=1 ... It is required to index all these words into a single column ... with the line numbers from which it came into a column alongside. ... need to know the line number of each word in the original document. ...
    (microsoft.public.excel.misc)
  • RE: Rearranging and Indexing a column array
    ... The first column ABCD is the first line and all cells in it get the index=1 ... It is required to index all these words into a single column ... with the line numbers from which it came into a column alongside. ... need to know the line number of each word in the original document. ...
    (microsoft.public.excel.misc)