Vlookup for negative values?

F

FeDude

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.
 
F

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
 
F

FeDude

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
 
F

FeDude

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?
 
P

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
 
F

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
 
M

Mark Graesser

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
[email protected]

----- 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?
 
F

FeDude...Dunce

Nevermind...

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

RTFM....

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