Excel - COLUMN Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Trusted by people who work at