VLOOKUP?? Help

littlemt

New Member
Joined
Oct 3, 2011
Messages
10
Hi,

I really need help.

I have an inventory list on one sheet. On the next sheet, I want it to only list items that I have more than one of.

I am using this formula to do that:
=+IF(Inventory!I6>=1,Inventory!$I$6,"")

But, everything shows up. How do I get it to not show anything if it is zero or blank.

Thanks in advance.

Littlemt
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Hi,

I really need help.

I have an inventory list on one sheet. On the next sheet, I want it to only list items that I have more than one of.

I am using this formula to do that:
=+IF(Inventory!I6>=1,Inventory!$I$6,"")

But, everything shows up. How do I get it to not show anything if it is zero or blank.

Thanks in advance.

Littlemt

Care to post a small sample of data?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Hi,

I really need help.

I have an inventory list on one sheet. On the next sheet, I want it to only list items that I have more than one of.

I am using this formula to do that:
=+IF(Inventory!I6>=1,Inventory!$I$6,"")

But, everything shows up. How do I get it to not show anything if it is zero or blank.

Thanks in advance.

Littlemt
Try this...

Data:

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:72px;" /><col style="width:72px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">Product</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Qty</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">19</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item2</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item3</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item4</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">69</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item7</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">24</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item8</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">59</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item9</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">58</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item10</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item11</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item12</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">8</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item13</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">79</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item14</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item15</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">6</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item16</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">78</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item17</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item18</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">39</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Item19</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">61</td></tr></table> <br /><br />
In the formulas I use the following defined named ranges:
  • Product
  • Refers to: =Sheet1!$A:$A
  • QTY
  • Refers to: =Sheet1!$B$2:$B$20
On Sheet2...

<b>Sheet2</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:72px;" /><col style="width:72px;" /><col style="width:72px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">Count</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Product</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Qty</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">11</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item1</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">19</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item5</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">69</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item7</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">24</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item8</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">59</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item9</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">58</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item12</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">8</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item13</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">79</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item15</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">6</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item16</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">78</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item18</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">39</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item19</td><td style="background-color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">61</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="background-color:#ccffcc; color:#ccffcc; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></table> <br /><br />
Enter this formula in A2. This will return the count of records that meet the criteria.

=COUNTIF(Qty,">0")

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)>A$2,"",INDEX(Product,SMALL(IF(Qty>0,ROW(Qty)),ROWS(B$2:B2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Enter this formula in C2:

=IF(B2="","",INDEX(Qty,MATCH(B2,Product,0)-1))

Select both B2 and C2 and copy down until you get blanks.

Here's a small sample file that demonstrates this.

zzzLittlemt.xls 19kb

http://cjoint.com/?AJywxeB2zjn
 

littlemt

New Member
Joined
Oct 3, 2011
Messages
10
THANKS SO MUCH!! I don't really understand what it all means, so I will have to digest it and apply it. I am grateful for you taking the time to help me.

I tried uploading my sheet here, if you are at all curious.

http://www.mediafire.com/?rwn2wt96zr9grya

i will give it a shot tonight.

THANK YOU THANK YOU THANK YOU AGAIN!!

Littlemt
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

THANKS SO MUCH!! I don't really understand what it all means, so I will have to digest it and apply it. I am grateful for you taking the time to help me.

I tried uploading my sheet here, if you are at all curious.

http://www.mediafire.com/?rwn2wt96zr9grya

i will give it a shot tonight.

THANK YOU THANK YOU THANK YOU AGAIN!!

Littlemt
I looked at your file...

Which column is the QTY column? Looks like there are 3 columns that contain some kind of qty value.
 

littlemt

New Member
Joined
Oct 3, 2011
Messages
10
OOHH, sorry for not clarifying.

I am trying to display results that have more than 1 QTY in column 'I'. The 'HP show' column....so really, the only items that should not show up so far is the last three items.

Littlemt.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

OOHH, sorry for not clarifying.

I am trying to display results that have more than 1 QTY in column 'I'. The 'HP show' column....so really, the only items that should not show up so far is the last three items.

Littlemt.
Hmmm...

There are only 3 numbers greater than 1 in column I on the Inventory sheet and none of those entries are the last entries.

:confused:
 

littlemt

New Member
Joined
Oct 3, 2011
Messages
10
Goodness....sorry for not being clear. i would like the 'HP Showing List' sheet to show items that have a QTY of 1 or more in column "I" on the 'inventory' sheet.

So, the HP sheet (if I can made it do what I want) would list all items except for lines 7(sku: ADA-NT-5-07-F; Queen Panel Footboard), 12 (sku:ADA-NT-5-17-F; EK Panel Footboard), 38 (sku: AUS-ME-6-70-0; GATHERING TILE TOP TABLE), 39 (sku: AUS-ME-3-55-0, LADDERBACK BARSTOOL), and 41 (SERVER).

I hope this makes more sense.

Thanks again for your help. I really REALLY appreciate it :)

Littlemt
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Goodness....sorry for not being clear. i would like the 'HP Showing List' sheet to show items that have a QTY of 1 or more in column "I" on the 'inventory' sheet.

So, the HP sheet (if I can made it do what I want) would list all items except for lines 7(sku: ADA-NT-5-07-F; Queen Panel Footboard), 12 (sku:ADA-NT-5-17-F; EK Panel Footboard), 38 (sku: AUS-ME-6-70-0; GATHERING TILE TOP TABLE), 39 (sku: AUS-ME-3-55-0, LADDERBACK BARSTOOL), and 41 (SERVER).

I hope this makes more sense.

Thanks again for your help. I really REALLY appreciate it :)

Littlemt
Ok...

I'm going to post this as a reminder so I'll see it later when I return.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top