How to Lookup First Non-zero value in a column and copy it to a cell in another sheet. and also the second Non-zero value in a column.
No need “copy” result to “to a cell in another sheet”
Just put to target cell formula
{=IFERROR(INDEX('<SheetName>'.A1:A1000;SMALL(IF(N('<SheetName>'.A1:A1000)<>0;ROW(A:A);"");1));"")}
This is an array formula, complete its entry by pressing Ctrl+Shift+Enter
Of course, you should change the sheet name and range to your desired
For the second, third, and so on values, change the last parameter in the SMALL function. For example, for the second value, the formula would be:
{=IFERROR(INDEX('<SheetName>'.A1:A1000;SMALL(IF(N('<SheetName>'.A1:A1000)<>0;ROW(A:A);"");2));"")}help me to create it, iam new to calc, pls help me
sheet name - LEDGER
COLUMN - G
CELL - FROM 1 to 23
{=IFERROR(INDEX($LEDGER.G1:G23;SMALL(IF(N($LEDGER.G1:G23)<>0;ROW($LEDGER.G1:G23);"");1));"")}
and
{=IFERROR(INDEX($LEDGER.G1:G23;SMALL(IF(N($LEDGER.G1:G23)<>0;ROW($LEDGER.G1:G23);"");2));"")}
Do not forget to finish entering the formula with the key combination Ctrl+Shift+Enter!
perfect, thank you,
the first and the second non zero value from column G is perfectly placed in it’s destination cell, well i need the name of that person from column B in the same row.
Eg. if the first non zero value is in G4 I need a formula to put the name from B4 to my target cell
and if the second non zero value is in G10 I need a formula to put the name from B10 to my target cell.
Column B represents the name of employees from G1 To G23
{=IFERROR(INDEX($LEDGER.G1:G23;SMALL(IF(N($LEDGER.G1:G23)<>0;ROW($LEDGER.G1:G23);"");1));"")}
Here, if i replace G1 to G29 and G23 to G51, will the formula work ?
It’s hard to say - I don’t know what your data looks like. Just try it.
=IFERROR(INDEX($LEDGER.B29:B51;SMALL(IF(N($LEDGER.G29:G51)<>0;ROW($LEDGER.G29:G51);"");1));"")
there is no option to attach a screen shot of my ledger.
what about my first query that i asked you just before that ?
ledger for the month of March first 25 rows
ledger for the month of April 25 TO 52 and so on. so i want to get that first non zero value of every month from my ledger sheet to my income and expense sheet of that month.
In the last formula, I changed column G to B at the beginning of the formula - should return a name.
in the last formula, its not B29 and B51, its B1 and B25 and G1, its working fine thank you…
What information you need to know to help me to create a formula for the next month
Sorry, but it seems to me that your last question is incorrectly worded. Perhaps you wanted to ask “What do I need to learn to apply this formula to different months myself?” Try to open YouTube and write “calc edit addresses in formulas” in the search bar. In 15-20 minutes you will know everything you need for this not very complicated operation.