Vlookup for negative values?

[Guest]

Is it possible to have a VLOOKUP that lookis in a range for the first instance where the value goes negative?

I have an economic table and I want to have a summary cell indicate the value of a corresponding column cell when a certain column goes negative.

 

[Frank Kabel]

Hi
one way:
=INDEX(A1:B100,MATCH(TRUE,(A1:A100)<0,0),2)
entered as array formula (CTRL+SHIFT+ENTER)
will return the value form column B for the first instance of a
negative value in column A

HTH
Frank

 

[Guest]

Frank

Thanks for the suggestion. Unfortunately I cannot seem to get the MATCH function to work. The logical operations you are performing are beyond me. Here is my syntax

=MATCH(TRUE, 'Money Flow'!U40:U56<0, 0

It always returns NA. Your comment about Cntrl+Shift+Enter is also confusing in your reply

 

[Guest]

Here is my data:

A B

3 A
2 B
1 C
0 D
-1 E
-2 F

Here is my MATCH Function: =MATCH(TRUE, (A2:A7)<0, 0)
Here is my INDEX Function: =INDEX(A2:B7,MATCH(TRUE,(A2:A7)<0,0),2)

Both return N/A

Any suggestions?

 

[Peo Sjoblom]

You have to enter the formula with ctrl + shift & enter

select the cell where you want the formula, type in the formula,
don't enter the formula with enter, hold down ctrl and shift and then hit
enter.
If done correctly you'll get { } around the formula and a correct answer

 

[Frank Kabel]

Hi
quite simple
just enter the formula as shown below. But instead of entering this
formula with the key 'ENTER' hit 'CTRL+SHIFT+ENTER' together. This will
create an array formula. as a result the formula will be preceded and
followed by a { and } bracket respectively

This should do it
Frank

 

[Guest]

FeDude,
You need to enter the formulas as arrays. To do this select the cell containing the formula, click in the formula bar at the top of the sheet (as if you were going to edit the formula), then press -Ctrl- -Shift- and -Enter- all at the same time (do not hit _eEnter- by itself).

Excel will know process this function as an array and you should get the result you are looking for.

For more information search on "Array Formulas" in Excel help.


Good Luck,
Mark Graesser
(e-mail address removed)

----- FeDude wrote: -----

Here is my data:

A B

3 A
2 B
1 C
0 D
-1 E
-2 F

Here is my MATCH Function: =MATCH(TRUE, (A2:A7)<0, 0)
Here is my INDEX Function: =INDEX(A2:B7,MATCH(TRUE,(A2:A7)<0,0),2)

Both return N/A

Any suggestions?

 

[Guest]

Nevermind...

I just read about teh Cntrl+Shift+Enter..

RTFM....

Thanks for all your help. Excellent solution....