FORMULA: something like VLOOKUP

[Creatives]

Hello here, first of all I would like to apologize for my english.

I have 2 columns (A= date; B=share price)
A.................B

1.1.12 ....... 1,5
5.1.12 ....... 1,3
8.1.12 ....... 1,8
10.1.12 ..... 1,4
13.1.12 ..... 2,1

In C1 I type the price of share and I need to get date when this share reached that value. For example:
In C1 I type: 1,8 and the result = 8.1.12.....I wanna type for example 2,0854 and the result would be 13.1.12

I tried INDEX(A1:A5; MATCH(C1; B1:B5; -1)) but when I type the value higher than first value (for example 1,8) it says #N/A

So could you help me to figure it out? I would very appreciate it. Thx!

[oeldere]

When you get #N/A the value is not available.

Change the blue cell in the attached file and see #N/A pops up.

[jason.b75]

Try

=index(A1:A5;min(if(B1:B5>=C1;row(B1:B5))))

Array confirmed with Shift Ctrl Enter.

[Richard Buttrey]

Hi,

=INDEX(A1:A5,MATCH(C1;B1:B5)+1;1)

[Creatives]

Its not working for values lower than first value(1,5)

[Creatives]

@jason

It works for A1:B5 but not for my bigger database. I have 1113 cells and its not working . Its really chaotic and I cant figure out wheres the problem.

[Creatives]

Example.xlsx
Attached file

[Creatives]

@oeldere,
It works only for exact values like 1,5; 1,3; 1,8 etc not for 1,44; 1,39

[oeldere]

And your result in #7 is correct, or we have to change the formula?

If there is no exact match, which value should be showed.

The next value above or the next value below?

[Creatives]

@oeldere,

Number(date) of value when value reached required price.
For example

1 ... 0,8
2 ... 1,4
3 ... 0,7
4 ... 1,1

requiered value = 0,9 so the result would be (2)
Imagine the curve..

So the answer is next value below but it doesnt matter I allways can add +1 to number

[oeldere]

I sorted the table.

See the yellow cells.

See if the result (217) is what you wanted.

[Creatives]

@oeldere,
I really appreciate your working but its wrong. The right answer with 0,6 would be 855.
It has to be first interval when value is reached so 0,6 is between 0,6181 - 0,5965 (856 - 855) I think
What about to create intervals? But I have no experience with intervals

[oeldere]

Maybe i can't help you with that (if it is depending on several intervals).

But how do you define which interval need to be found (first / 2nd or 3th)?

[Creatives]

@oeldere,

It has to be the FIRST one. Imagine days and values... day by day, value by value. From the top goes days to the bottom . You wanna sell share when share reaches required price. So you say: I wanna sell this share when reaches 0,9. So first time when share reaches that value is at 1061 (0,9146 - 0,8928)

[oeldere]

See if this is, what you're up to.

See the yellow cell in the attached file.

[Creatives]

@oeldere,
We are at the beginning. When I type value higher than first one it (1,044) it says #N/A (

[oeldere]

Unfortanly i don't have an answer for your question.

Maybe wiht a new view later this week.

[Creatives]

@oeldere,
I really appreciate it.
ANy ideas someone else? :-(( I am really desperate

[jason.b75]

The array formula needs to be adjusted to allow for the index range not starting in row 1.

Please Login or Register  to view this content.

[Creatives]

I figure it out. Your Formula really helped me!
I added 2 cells (Stop limit and Stop loss) (max value and min value) for max value I am using formula: =INDEX(A3:A1115,MIN(IF(B3:B1115>=C1,ROW(B3:B1115)-ROW(B3)+1,"Neznámá chyba"))) it works for values higher than first one. And for min value I am using =INDEX(A3:A1115;MATCH(C2;A3:A1115;-1)) it works for values lower than first.


SOLVED