vlookup for only column with values

[bmpreston]

So I'm trying to use a vlookup to check a table of a few values, and only print the numbers if the value is more than zero.

Column A returns the value in Column C IF Column D is at least 1 or higher.

If Column D is zero, Column A skips the Column C value.

I have approx 3000 rows to validate. The copying of the formula is of course quite easy. Just need the base formula.

Thanks in advance.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Blank[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]NEEDED FORMULA (1000)[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Needed formula (1001)[/TD]
[TD][/TD]
[TD]1001[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Needed Formula (1003) (SKIPS 1002)[/TD]
[TD][/TD]
[TD]1002[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Needed Formula (1004)[/TD]
[TD][/TD]
[TD]1003[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Needed Formula (1006) (Skips over 1005)[/TD]
[TD][/TD]
[TD]1004[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Needed Formula (Etc)[/TD]
[TD][/TD]
[TD]1005[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Needed Formula (Etc)[/TD]
[TD][/TD]
[TD]1006[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

Just give the name rngD to your cells D2:D8, and use the following Array Formula in cell A2 :

=IFERROR(IF(ROWS($2:2)<=COUNTA(rngD),INDEX(OFFSET(rngD,0,-1),SMALL(IF(rngD<>0,ROW(rngD)-MIN(ROW(rngD))+1),ROWS($2:2))),""),"")

HTH

 

[bmpreston]

Can you help break down what this does? I'm totally confused and my cells can't be named rngD like you mention.

Column A is for the values to be populated.
In specific, I'm using Column U, from U2:U3000 (as my column C)
for the Values to validate if Z2:Z3000 (Column D) have any value higher than 0.

Thanks

 

[James006]

No problem you can use the name rngZ for cells Z2:Z3000 and the formula is now

[COLOR=#333333]=IFERROR(IF(ROWS($2:2)<=COUNTA(rngZ),INDEX(OFFSET(rngZ,0,-5),SMALL(IF(rngZ<>0,ROW(rngZ)-MIN(ROW(rngZ))+1),ROWS($2:2))),""),"")[/COLOR]

Use simultaneoulsly the 3 keys Control+Shift+Enter instead of the Enter key ... for this Array Formula ...

HTH

 

[bmpreston]

I guess my question is what is a range? What is Range Cell or rngz?

I have never heard of such a thing.

Thanks

 

[James006]

No problem ...

The initial idea is to give a name to a range of cells ...

see 403 Forbidden

HTH

 

[bmpreston]

Ok, I found how to give a range. But I'm getting the value of that cell with the number above zero.

Using the above example, I'm trying to use Column D, to copy Column C over.

Right now it's copying column D over. I wish I understood more of this formula to know how to change WHAT gets copied. That seems like a VLOOKUP equation now. The corresponding row, differing columns...

Thanks

 

[James006]

Now that you have managed to give the Name rngZ to your cells range Z2:Z3000 ...

The formula in post #4 must appear in cell A2 ... and should return the Values in Column U ... as requested ...

HTH

 

[bmpreston]

Dropbox - 12 Month Inventory Tracker edited.xlsx

That's the exact file. It just gives me a 16. I don't know where that number comes from. I added comments pointing to what I expect it to do.

Thanks

 

[James006]

Sorry ... but you did not use ... as proposed ... the formula shown in post #4 ....

Your exact formula in cell A3 should be :

=IFERROR(IF(ROWS($3:3)<=COUNTA(rngZ),INDEX(OFFSET(rngZ,0,-5),SMALL(IF(rngZ<>0,ROW(rngZ)-MIN(ROW(rngZ))+1),ROWS($3:3))),""),"")

HTH