Find Cell Value in Data and Return Value of the Cell to the Right

[Bkisley]

Here is an example data set - a series of cash flows

A1: 0 A2: -50 A3: -25 A4: 50 A5: 80 A6: 100

I need the following...
In the first cell (B1) I need to have the first year of cash flow regardless of whether the cell is < or > 0
In the next cells (B2) I need to have an equation that looks up the value in B1 in the series A1:A6 and then return the cell value to the right of that value.

Needed output
B1: -50 B2: -25 B3: 50 B4: 80 B5: 100 B6:0 B7...:0


A different set of data...
A1: 0 A2: 0 A3: 0 A4: -50 A5: 20 A6: 30 A7: 50 A8: 90

Needed Output
B1: -50 B2: 20 B3: 30 B4: 50 B5: 90 B6:0 B7....:0


THANK YOU!

 

[pbornemeier]

The formula in B1 will be =A2
but you say
In the next cells (B2) I need to have an equation that looks up the value in B1 in the series A1:A6 and then return the cell value to the right of that value.
Looking up the value in B1 (-50), it is found in A2 and the cell to the right of A2 is B2....which is where the formula will go....which makes a circular reference.
Please restate your request.

 

[Bkisley]

wow ok I see what I did....let me correct this!

Here is an example data set - a series of cash flows

A1: 0 B1: -50 C1: -25 D1: 50 E1: 80 F1: 100

I need the following...
In the first cell (B1) I need to have the first year of cash flow regardless of whether the cell is < or > 0
In the next cells (B2) I need to have an equation that looks up the value in B1 in the series A1:A6 and then return the cell value to the right of that value.

Needed output
A2: -50 B2: -25 C2: 50 D2: 80 E2: 100 F2:0 G2...:0

 

[pbornemeier]

A2 = =B1
B2 = =IFERROR(INDEX($A$1:$F$1,,MATCH(A2,$A$1:$F$1,0)+1),0)
and drag-copy B2 to C2:F2

Problem is that if a number in A1:F1 is repeated, the formula will always return the value to the right of the leftmost instance of the repeated number.

 

[heehaa]

Not sure if this is what you want

=IFERROR(INDEX(1:1,SMALL(IF(1:1<>0,COLUMN(1:1)),COLUMNS($A$1:A1))),"")

and then CTRL SHIFT ENTER

Drag across A2:D2 or more