-1

Lets say I have 1000s of rows in column C with any integer 1-100. Everytime a 5 is found in column C is found I want the value in Column A displayed in Column D.

So if after 1000 rows I have found the number 5, ten times. I would like cells D1:D10 to display the value of column A for when Column C is 5.

Sorry for being so dumb.

user2748840
  • 1

2 Answers2

1

One possibility is the array function below. Enter that in D1 and because its an array function, press ctrl+shift+enter instead of just enter.

=INDIRECT("A"&SMALL(IF(C:C=5,ROW(C:C),""),ROWS(1:$1)))

If you've entered it correctly it will look like this:

={INDIRECT("A"&SMALL(IF(C:C=5,ROW(C:C),""),ROWS(1:$1)))}

Hope that helps!

doovers
  • 8,545
  • 10
  • 42
  • 70
0

If you are willing to hide a helper column I have a solution (would work without it, but formulas would be nasty).

Helper column (D):

D1 = MATCH(N, RANGE, 0) 
D2 = D1 + MATCH(N, OFFSET(RANGE, C1, 0), 0)  // NOTE: range must be use absolute references for the top $C$1:C#

Fill the D2 formula down. This column will have errors, but we dont care as it will be hidden

Result Column (E):

E1 = IFERROR(INDEX(A_RANGE, D1), "") // NOTE: like above, use absolute reference for top $A$1:A#

Fill it down, that should do the trick.

Ross Larson
  • 2,357
  • 2
  • 27
  • 38
  • I tried this method but it didn't work probably because I didn't fully describe my problem. For every 52 rows I used the exact same list of 52 numbers and then would start over again. Here is the problem I'm trying to solve: For a 5 card poker hand, using blackjack scoring, except an ace is always worth 11, what is the % you are dealt a 37? What is the % of being dealt below a 37 and over a 37? Using my much more inefficient method, especially limited by the lack of columns in Open Office, I got 6.31% for 37, 45.83% below, and 47.86% above in 4914 hands. – user2748840 Sep 05 '13 at 11:47
  • But I feel this is wrong because rand doesn't seem to work properly. My mean is 36.92 which is far off from the correct 36.54 especially for that many samples.... – user2748840 Sep 05 '13 at 11:49
  • If I had Excel I could of had 50,000 hands for the same amount of work for the method I used... but if the rand function is not working properly than it wouldn't matter anyways. – user2748840 Sep 05 '13 at 11:55
  • Looks like excel rand isnt very good, Id wager open office rand is even worse. http://stackoverflow.com/questions/5886237/how-good-is-the-rand-function-in-excel-for-monte-carlo-simulation – Ross Larson Sep 05 '13 at 16:30
  • are you sure it wouldnt work? It can find duplicates in column C, that is what the offset is for, to move the range down to start after teh last match – Ross Larson Sep 05 '13 at 16:33