0

I am trying to compare a value (for example if D4 >= 10) and if this is true have it pull the data in the column. This is to pass information from one table to another in the same excel sheet. For some reason I cannot seem to do this for a whole range of cells without having a lot of empty space. I tried a very basic formula which works but leaves a bunch of cells empty. This formula is the following: 

=IF(D4>=10, C4, "").

I tried VLOOKUPS and many other referencing formulas like INDEX but cant seem to make it work with the if condition. I know this is a very basic question but was wondering if anyone can help me out with that.

Just to be clear what I am trying to do is to pull the data in the C column if the values in the D column are greater than or equal to 10. This is from one table to another in the same sheet but without any empty cells. So that it pulls all the values in the C column when the D column has a value of 10 or more in a list.

Thank you in advance.

CallumDA
  • 12,025
  • 6
  • 30
  • 52
Josh
  • 77
  • 1
  • 8
  • If D4 is not >= 10, you will make your target an empty cell with that formula. What do you want your target to be if D4 is not >= 10? – tysonwright Mar 13 '18 at 23:14
  • If it is not greater or equal to 10 then I do not want it to do anything, it can leave the cell blank. It could be a sorting issue however the data I am trying to pull when it is >= 10 is text and numbers, so i am not sure sorting could fix it. – Josh Mar 14 '18 at 16:38

2 Answers2

1

Something like this? The numbers in column D are moved to column F (for example), if greater than or equal to 10, and sorted from largest to smallest:

=AGGREGATE(14,6,($D$1:$D$15>=10)*$D$1:$D$15,ROW(1:1))

enter image description here

CallumDA
  • 12,025
  • 6
  • 30
  • 52
  • This works pretty well now the problem is that I need it to analyze the information from the D column put pull the information from the C column. For example when D6 >= 10 I need to to take the text from the C column. An example text could be ATL180 and thats what it pulls from the C column when the number in the D column is >= 10 – Josh Mar 14 '18 at 18:21
  • @josh, that makes sense, I’ll edit my answer in a bit :) – CallumDA Mar 14 '18 at 18:52
0

Updated based on clarified question:

You need to add a column that does this - let's say it's column F, so the formula in F1 is:

=IF(AND(D1 >= 10, ISNUMBER(D1)), C1, "")

Enter this into the first cell of your target location, then click in the formula bar and press Ctrl + Shift + Enter to enter it:

=IFERROR(INDEX(F:F,SMALL(IF($F$1:$F$15 <> "",ROW($F$1:$F$15)),ROW()-ROW($F$1)+1)),"")

Then drag that cell down to fill the columns you want.

tysonwright
  • 1,525
  • 1
  • 9
  • 19
  • This works, however I would still have a lot of blank spaces in between the data I pull and what I am trying to pull from Column C is text and number for example ATL180. I want the formula to analyze the information in Column D and if that number is greater or equal to 10 then pull the information from column C. – Josh Mar 14 '18 at 18:28
  • So if the value is ATL180, you want it to treat that as 180? – tysonwright Mar 14 '18 at 18:38
  • Ill try to clear it up a bit better. In column D I have scores, when these scores are greater than or equal to 10 I want it to pull the ATL180 from column C into the cell that I am putting this formula in. Basically I want the cell that I put the formula in to equal the ATL180 in column C when the score in column D if >= 10. I trying to make it so that there is no empty spaces in between. So I may have a score of 10 in D6 and D10 so I want the info. from C6 and C10 to populate in the first two cells of the column I input the formula in. – Josh Mar 14 '18 at 18:45
  • The only issue left now is that its pulling all the data and I dont really see where I an add the rule in the formula so that it only pulls when D column is greater or equal to 10. – Josh Mar 14 '18 at 19:57
  • Did you use both parts of the code I posted? Because the first part (that you paste into column F) only pulls C4 when D4 is numeric and greater than or equal to 10. – tysonwright Mar 14 '18 at 19:59
  • I apologize I had not seen that. So I changed it to the Y column and added that formula there. I dragged the formula so that it would populate all the info I need (up to Y40). Then I changed the formula to the following: =IFERROR(INDEX(Y:Y,SMALL(IF($Y$1:$Y$40 <> "",ROW($Y$1:$Y$40)),ROW()-ROW($Y$1)+1)),""). For some reason its not pulling anything – Josh Mar 14 '18 at 20:23
  • Did you enter that value, then click in the formula bar, then press Ctrl + Shift + Enter? (You have to do that to make it an array formula, otherwise this doesn't work. Just hitting Enter won't do it. Hitting Crtl + Shift + Enter if you haven't first clicked in the formula bar doesn't do it either.) – tysonwright Mar 14 '18 at 20:35
  • I did yes and it adds the extra { } at the start and end of the formula but I am not quite sure why it isnt pulling the data. The first formula did work and the data is populating in the Y column just not in the column where I enter the big formula. – Josh Mar 14 '18 at 20:42
  • Without seeing your actual spreadsheet I don't know what to tell you, because this works in my test spreadsheet. – tysonwright Mar 14 '18 at 20:56
  • I realized the problem is because I am placing the formula in the AJ11 instead of AJ1 and thats why it isnt populating is there a change I can make so that it works starting on AJ11 or should I just start at AJ1 to avoid more trouble? – Josh Mar 14 '18 at 21:38
  • Where it says `ROW()-ROW($Y$1)+1` change it to `ROW()-10-ROW($Y$1)+1` – tysonwright Mar 14 '18 at 22:19