# VLOOKUP?? Help

#### littlemt

##### New Member
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Aladin Akyurek

##### MrExcel MVP
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
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:

Book1
AB
1ProductQty
2Item119
3Item2_
4Item3_
5Item4_
6Item569
7Item60
8Item724
9Item859
10Item958
11Item100
12Item110
13Item128
14Item1379
15Item140
16Item156
17Item1678
18Item17_
19Item1839
20Item1961
Sheet1

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

Book1
ABC
1CountProductQty
211Item119
3_Item569
4_Item724
5_Item859
6_Item958
7_Item128
8_Item1379
9_Item156
10_Item1678
11_Item1839
12_Item1961
13___
Sheet2

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

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

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.

#### littlemt

##### New Member
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
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.

#### T. Valko

##### Well-known Member
Ok...

I'm going to post this as a reminder so I'll see it later when I return.
OK, here's your file with these formulas implemented. The formulas are slightly different than those I used in the sample file I posted.

zzzInventorytest.xlsx 23kb

http://cjoint.com/?AJzvvzxe50S

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,168,186
Messages
5,857,845
Members
431,900
Latest member
Paradime0346

### 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

### 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