Home |
Search |
Today's Posts |
#1
|
|||
|
|||
lookup nonzerovalues
I have two columns A and B. Column A contains ascending numbers from 1 to
100, column B contains arbitrary values (not in ascending order). What is the function that looks for the first nonzero value in column B (going from the frist to the last row) and returns the value of column A of the same row? |
#2
|
|||
|
|||
Hi David
=INDEX($A$1:$B$1000,SMALL(IF($B$1:$B$1000<0,ROW($ B$1:$B$1000),""),1),1) entered using control & shift & enter not just enter Cheers JulieD "DAVID" wrote in message ... I have two columns A and B. Column A contains ascending numbers from 1 to 100, column B contains arbitrary values (not in ascending order). What is the function that looks for the first nonzero value in column B (going from the frist to the last row) and returns the value of column A of the same row? |
#3
|
|||
|
|||
Another one:
=INDEX($A$1:$A$1000,MATCH(TRUE,$B$1:$B$1000<0,0)) Still hit ctrl-shift-enter with this array formula. Change that 1000 to be as big as you need, but you can't use the whole column. DAVID wrote: I have two columns A and B. Column A contains ascending numbers from 1 to 100, column B contains arbitrary values (not in ascending order). What is the function that looks for the first nonzero value in column B (going from the frist to the last row) and returns the value of column A of the same row? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a lookup table with an added varable? | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions | |||
Index table lookup anomaly | Excel Worksheet Functions | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |