Formulas to Retrieve Data from Columns

Try this (untested):

=--IF(I2>=12,IF($J2<=100,0+SUBSTITUTE(IF($D3>=125,","&$D3,"")&IF($E3>=125,","&$E3,"")&IF($F3>=125,","&$F3,""),",","",1),""),"")

I am still having problems,, but want to thank you very much for your help so far. With your help I made progress. However, I have two columns that I cannot get to work. I am probably just not seeing the mistake I have made.
Hopefully another set of eyes can help me. The two "ID#" columns I cannot get to work are: AS for 140 Avg w/175 game (M) and BW for Series 140 Pins Over Avg (S).
I get "N/A" instead of the ID number. I am again attaching my worksheet since I was able to make changes to the other columns that worked thanks to your help.

I really would like to understand why there are three computations in my worksheet that just don't seem to work and I really want to find the solution.

In Column AS (ID #) (140 Avg w/ 175 Game), the bracketed formula is[=IF(AR2="","",SMALL(IF(INDEX($M$2:$M$600,,MATCH(LEFT(AQ2,FIND("#",AQ2)-2),$M$1:$M$1,0))=AR2,ROW($M$2:$M$600)),COUNTIF(AR$2:AR2,AR2)))] -- but it doesn't work. In Column AN (ID #) (120 Avg w/150 Game) the bracketed formula is [=IF(AM2="","",SMALL(IF(INDEX($L$2:$L$600,,MATCH(LEFT(AL2,FIND("#",AL2)-2),$L$1:$L$1,0))=AM2,ROW($L$2:$L$600)),COUNTIF(AM$2:AM2,AM2)))] -- this one works. Why do they both not work?
,
The next problem is in column BQ (Score) (45 pins over average). If I use the formula [=IFERROR(LARGE($R$2:$R$600,ROW(BQ1)),(""))] I get a blank cell. If I put in the cell number [=R33], I get an avg from column 4 plus the next three columns with formulas work.

The last column with a problem is column BV where using the same formula as above except using column S and BV, it works, but BW doesn't work.

This is a real mystery and I would really like to get this working. I have downloaded the file on my previous entry. I did not get any responses and so I have expanded by narrative in hopes that someone out there will be able to answer my questions/problems.

Thank you.

AS2: match() is not finding "140 Avg w/175 Gm" in cell M1 because M1 says: "140 Avg w/175 Game" "Gm" doesn't match "Game"

BQ2: large() fails because ALL "numbers" in col-R are text. Fix as explained in posts #2, 4 and/or 6

BW2: match() is not finding "Series 140 Pins Over Avg" in cell S1 because "Series 140 Pins Over Avg " in S1 has a spurious 'space' character at the end of the text.

First of all, thanks for the rep

I'm going to ask a question to test my understanding of what you expect before I say anything else!

If col-R of your workbook was populated as follows with all other rows in col-R being blank:

Cell R2: 10
Cell R3: 20 30
Cell R4: 25

What would your expected values be for cells BQ2, BQ3, BQ4, BQ5?

Sorry, my question was based on a fictitious hypothetical example that was just trying to boil down an example to its simplest possible terms.

There are instances in col-R where you have more than one number in a cell, for example R374 looks like: 250 250 250
I'm just trying to figure out how you expect col-BQ to deal with this circumstance.

So back to my post #11 question:
(a) would you expect:
BQ2: 25
BQ3: 10
BQ4: blank
BQ5: blank

or (b) would you expect:
BQ2: 30
BQ3: 25
BQ4: 20
BQ5: 10

(a) is what would actually happen, but I suspect you may be looking for (b) to happen (or maybe something else!).

The chances of the same number is all three columns is slim to none. I used that number in order to get a result in a different column. Therefore, what I want to happen is:

BQ 2 is the highest score that will be picked up from column R
BQ 3 is the the next highest score that will be picked up from Column R

so I guess that would mean I would expect your (b). However, once the numbers from Column R are gone, I want the columns BQ 4-32 to show nothing at all.

"BQ 2 is the highest score that will be picked up from column R"

The catch is that the large() function in column-BQ will IGNORE column-R cells that contain 2 or more "numbers". I put "numbers" in quotes because a cell like: "123 456" does not contain 2 real numbers it contains a single text string "123 456" and the large() function ignores cells containing text.

Given the nature of the formula in Column-R, a single cell in that column can contain one or two or three numbers. When there is more than number in a single column-R cell then the col-BQ formula will NOT work as you wish. The large() function in column-BQ will ONLY consider cells that contain a SINGLE number and ENTIRELY IGNORE all column-R cells that contain two or three numbers (that are in fact NOT really numbers, they are just text that looks like numbers).

If you really need two or more "numbers" in a single column-R cell then I don't see a way to get Column BQ to behave the way you want. Is multiple numbers in a single cell a firm requirement? If not is there a simpler approach? Maybe something like =max(D3:F3) in column-R ??

Column R works, however, it does not work with BQ, which is the problem. As I stated before, I only used the three large scores to make a different column work. The chances of a single
line in column R having more than one score is remote and I can deal with it if it happens. I have deleted the one score of three identical scores. Column R takes scores from lines in Columns D:F. It
sees if any of them are 45 pins over average. If it is 45 pins over average, then it will appear in Column R and in order by highest to lowest in Column BQ. Then in column BR through BT
it will give me the information of who the person is. Since my columns seem to be text, not numerical, I was told to use the substitute formula, but I do not know how to use that
formula when the information is gathered from three different columns. The important thing is that all three columns (D:F) will not show multiple numbers in Column R.

I am again attaching my worksheet.

Here is what I want in column BQ - BT:

Line# BQ BR BS BT
2. 251 373 18 RR RA
3. 200 353 17 QQ QA
4. 194 140 7 GG AG

Continuing down until all the scores in Column R are filled in in Columns BQ - BT.

Hopefully you can understand what I am saying.

"The important thing is that all three columns (D:F) will not show multiple numbers in Column R."

OK, I have a brand new column-R formula for you. It is simpler, it does not have the "text-that-looks-like-a-number" problem and, most importantly, it matches your col-BQ expected results

In R3 copied down:

Formula:

Please Login or Register  to view this content.

BR2 is 374 vs your expected result of 373, but as the 251 value comes from row-374 then I think 374 is the correct result. Cols BS and BT match your expected results.

The attached workbook is your post #16 attachment with the above mentioned changes made.

Hopefully (very hopefully ) this works for you. Let us know.

Thanks for the feedback - really glad it works for you!