**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need to create a rows in Sheet 1 where Sheet 2 column c has values in Sheet 2 column D that are greater than .01 and leave out items in Sheet 1 where Sheet 3 column D are = 0 (without using a filter, then copy/paste).

Solved by I. Y. in 21 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
08/06/2018 - 08:22

Hello and welcome to Got it! :)

User
08/06/2018 - 08:22

Hello

User
08/06/2018 - 08:23

Did you see my question, or do I need to input?

Excelchat Expert
08/06/2018 - 08:23

I've read your concern, but I hope we could have a sample data we could work on to get a better grasp of the situation. :)

User
08/06/2018 - 08:24

OK. Let me input

Excelchat Expert
08/06/2018 - 08:24

Okay thanks! :)

User
08/06/2018 - 08:25

I want to show a list on Sheet 2 of items in column c where items in another column are greater than 0

User
08/06/2018 - 08:25

in my example, only row 3 would show up

User
08/06/2018 - 08:26

without using filters, copying and pasting

Excelchat Expert
08/06/2018 - 08:27

Oh Okay I see. Basically, you want to fetch data from column C if its corresponding cell in column D is greater than 0, correct? Basically, in your data, it should only be Airfare that will show up. Is this right? :)

User
08/06/2018 - 08:27

Yes

User
08/06/2018 - 08:27

Need to show that on sheet 2

Excelchat Expert
08/06/2018 - 08:28

Okay I see! Just to clarify, it's only data in column C that we need to get from the table? :)

User
08/06/2018 - 08:28

If I can understand how to list just those items........I can build the rest

Excelchat Expert
08/06/2018 - 08:28

Okay nice then! Let me work on it for a while then, and update you the soonest! :)

User
08/06/2018 - 08:29

cool.........thanks!

Excelchat Expert
08/06/2018 - 08:32

Already done! Please see sheet 2 for your review. :)

User
08/06/2018 - 08:33

Geez........I thought I was good with formulas

Excelchat Expert
08/06/2018 - 08:33

The formula used is an array formula combining INDEX, SMALL, IF and ROW functions of Excel. :)

User
08/06/2018 - 08:33

I saw this online, but couldn't quite follow the logic

Excelchat Expert
08/06/2018 - 08:34

I also incorporated an IFERROR statement so that when there's no more data that matches your criteria, it will return a blank, which is in the cells below. :)

Excelchat Expert
08/06/2018 - 08:34

Oh I see, okay. :)

Excelchat Expert
08/06/2018 - 08:34

I can try to explain if you wish to dissect the formula. :)

User
08/06/2018 - 08:34

So awesome!!!!

User
08/06/2018 - 08:35

I'd love to understand better if possible

Excelchat Expert
08/06/2018 - 08:36

Okay, sure thing! We start with the INDEX function that returns a cell value after we specify a row and column offset.

User
08/06/2018 - 08:36

so, index the column itself (range)

Excelchat Expert
08/06/2018 - 08:37

yes. :)

User
08/06/2018 - 08:37

then what does small do?

Excelchat Expert
08/06/2018 - 08:38

the SMALL function returns the "nth smallest" value depending on the user-defined "n"

Excelchat Expert
08/06/2018 - 08:38

For example, if we have =SMALL(A1:A10,5), it returns the 5th smallest value from the A1:A10 range. :)

Excelchat Expert
08/06/2018 - 08:40

It is in this SMALL that we specify the criteria of our data, which is that we will only consider to look at values in column C if their corresponding value in column D is more than 0.

User
08/06/2018 - 08:40

I'll have to look at that one closer

Excelchat Expert
08/06/2018 - 08:40

That is denoted by the =IF(0<Sheet1!$D$3:$D$457 statement. :)

User
08/06/2018 - 08:42

pretty complex..........I'll figure it out.......I usually do, but I needed to see it........

Excelchat Expert
08/06/2018 - 08:42

yes you can! It's actually a pretty simple formula once you understand how each function works. :)

User
08/06/2018 - 08:43

I'm content with the feedback, response, time, etc. Thank you so much for your help!

Excelchat Expert
08/06/2018 - 08:43

Nice! If all is good now, please do end the session via the button on the upper right of your screen (sorry, can't do it from our end). Please do leave a good feedback upon your exit. Thanks so much and have a nice day! :)

Excelchat Expert
08/06/2018 - 08:43

Btw, you can download a copy of the document preview by going to File > Download as > Microsoft Excel (.xlsx). The formulas will be downloaded as well. :)

User
08/06/2018 - 08:44

cool!

User
08/06/2018 - 08:44

Thanks again. Have a great day!

Excelchat Expert
08/06/2018 - 08:44

You as well! :)

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*