Solve for 2nd Blank Match

[theBloodyNine]

I have a formula that works to solve and find my first MATCH. It is as follows:

=INDEX(E31:E80,MATCH(TRUE,G31:G80="",0))

If it helps to understand, the data is for an auction. The formula looks into column G31:G80 to see what item(s) have not been sold yet. The items price is put into the cell after purchase. These items will not be sold in order. So Item 5 might be the 1st item sold. Therefore Item 1 in Cell G31 would still be the right answer.

It then returns a persons name (TEXT) from column E31:E80. The names in column E31:E80 are listed in order of value, but there is no value associated with them. The name is the answer I need. That being said I do have a ranking Column D that Ranks them from 1-50 next to each name if that helps.

However once items are sold, what I cannot solve and would be grateful for help with, is 3 things:

1) In another Cell: I need a formula to find the 2nd "" Blank where the items is not yet sold and there is no price entered. The list cannot be reordered after something is entered.


Column D Column E Column G
1 Dave Smith $25
2 Mike Davis
3 Bob Jones $45
4 Kramer Mann Solve For the name that matches the 2nd Blank
5 Stacey Howl $17
6 Dana Hecht $29
7 Mark White Solve For the name that matches the 3rd Blank



2) In another Cell: I need a formula to find the 3rd "" Blank where the items is not yet sold and there is no price entered.

3.) In another Cell: I need to find the difference in the ranking between the blanks. For example: Mark White is 5 positions lower than Mike Davis. i.e. the drop off in quality.

 

[jasonb75]

1 and 2. Try replacing your existing formula for the first match with this one, then drag it down for the second, third, etc.

=IFERROR(INDEX(E:E,AGGREGATE(15,6,ROW($G$31:$G$80)/($G$31:$G$80=""),ROWS(B$2:B2))),"")

3. Try a formula on the basis of =MATCH("Mark White",D:D,0)-MATCH("Mike Davis",D:D,0)

 

[Eric W]

Welcome to the Board!

I'm a little unsure as to your requirements, but see if this is close:

Book1
	D	E	F	G	H	I	J
30	Name			Sale Price
31	Dave Smith			$25
32	Mike Davis
33	Bob Jones			45
34	Kramer Mann					Mike Davis	2
35	Stacey Howl			17
36	Dana Hecht			29
37	Mark White					Kramer Mann	3
Sheet4

Cell Formulas
Range	Formula
I31	=IF(G31>0,"",IFERROR(INDEX(D:D,AGGREGATE(14,6,ROW($G$30:$G30)/($G$30:$G30=""),1)),""))
J31	=IFERROR(ROW()-MATCH(I31,$D$30:$D30,0)-ROW($D$30)+1,"")

It's along the same lines as Jason proposed.

 

[Toadstool]

Another answer in the same flavor. I'm not sure if I'm understanding the "Dropoff" requirement so I'm listing relative row numbers then their difference in J and K to make it easier to follow.

Copy the formula down 2 rows to replace your INDEX and get the next 2 empty Price rows, or copy it down 20 to get the 1st through 20th.

	D	E	F	G	H	I	J	K
30	No.	Name		Price		Not Sold	1	Dropoff
31	1	Dave Smith		$25		Mike Davis	2	1
32	2	Mike Davis				Kramer Mann	4	2
33	3	Bob Jones		$45		Mark White	7	3
34	4	Kramer Mann
35	5	Stacey Howl		$17
36	6	Dana Hecht		$29
37	7	Mark White

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I31 =INDEX($E:$E,AGGREGATE(15,6,ROW($E$31:$E$80)/($G$31:$G$80=""),ROWS($I$30:$I30)))&"" J31 =(INDEX($D:$D,AGGREGATE(15,6,ROW($E$31:$E$80)/($G$31:$G$80=""),ROWS($I$30:$I30)))&"") K31 =IF(J31<>"",J31-J30,"") <tbody> </tbody>

<tbody>
</tbody>

 

[theBloodyNine]

Thank you all for the help. I apologize as I am not that experienced with Excel. However that is why am here to learn. I was not quite able to make those formulas work for me. I just got zero as an answer. I am pasting a better explanation below. This is the real world problem, and yes I work in the Fantasy Football business. I need to list the Best Remaining QB, the 2nd Best QB and the 3rd best QB that have not yet been drafted in a real time Auction Draft. So as some is selected, the "Actual Sale ($$$) price is entered. I need it to show up as listed below:
Best Remaining QB Overall Rank 2nd Best Remaining QB Overall Rank 3rd Best Remaining QB Overall Rank Deshaun Watson 2 NEED TO SOLVE ? ? NEED TO SOLVE ? ? The Above Answer is Correct This Answer Should be: This Answer Should be: 2nd Best Remaining QB Overall Rank 3rd Best Remaining QB Overall Rank Deshaun Watson 2 Jared Goff 8 Rank Player Name (QB) Actual Sale ($$$) 1 Patrick Mahomes $ 35 2 Deshaun Watson 3 Aaron Rodgers $ 25 4 Matt Ryan 5 Carson Wentz $ 15 6 Baker Mayfield $ 35 7 Kirk Cousins $ 45 8 Jared Goff 9 Dak Prescott 10 Russell Wilson 11 Cam Newton <colgroup><col><col><col><col><col span="2"><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[theBloodyNine]

Sorry: 2nd best should be Matt Ryan as listed below Thank you all for the help. I apologize as I am not that experienced with Excel. However that is why am here to learn. I was not quite able to make those formulas work for me. I just got zero as an answer. I am pasting a better explanation below. This is the real world problem, and yes I work in the Fantasy Football business. I need to list the Best Remaining QB, the 2nd Best QB and the 3rd best QB that have not yet been drafted in a real time Auction Draft. So as some is selected, the "Actual Sale ($$$) price is entered. I need it to show up as listed below: Best Remaining QB Overall Rank 2nd Best Remaining QB Overall Rank 3rd Best Remaining QB Overall Rank Deshaun Watson 2 NEED TO SOLVE ? ? NEED TO SOLVE ? ? The Above Answer is Correct This Answer Should be: This Answer Should be: 2nd Best Remaining QB Overall Rank 3rd Best Remaining QB Overall Rank Matt Ryan 4 Jared Goff 8 Rank Player Name (QB) Actual Sale ($$$) 1 Patrick Mahomes $ 35 2 Deshaun Watson 3 Aaron Rodgers $ 25 4 Matt Ryan 5 Carson Wentz $ 15 6 Baker Mayfield $ 35 7 Kirk Cousins $ 45 8 Jared Goff 9 Dak Prescott 10 Russell Wilson 11 Cam Newton <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

As you've seen, a good example makes it much easier to find a solution. See if this works for you:

Book1
	B	C	D	E	F	G	H	I	J
26		Best Remaining QB	Overall Rank		2nd Best Remaining QB	Overall Rank		3rd Best Remaining QB	Overall Rank
27		Deshaun Watson	2		Matt Ryan	4		Jared Goff	8
28
29
30		Rank	Player Name (QB)	Actual Sale ($$$)
31		1	Patrick Mahomes	$35
32		2	Deshaun Watson
33		3	Aaron Rodgers	$25
34		4	Matt Ryan
35		5	Carson Wentz	$15
36		6	Baker Mayfield	$35
37		7	Kirk Cousins	$45
38		8	Jared Goff
39		9	Dak Prescott
40		10	Russell Wilson
41		11	Cam Newton
Sheet4

Cell Formulas
Range	Formula
C27	=INDEX($B:$D,AGGREGATE(15,6,ROW($E$31:$E$41)/($E$31:$E$41=""),INT((COLUMNS($C:C)+2)/3)),3-MOD(COLUMNS($C:C)-1,3))&""

I was faced with creating either 2 formulas, or one slightly more complicated formula. I went with the single formula. In the layout above, enter the formula in C27. Then copy it and paste it to D27:J27. (Or drag the cell left, whatever is easiest for you.) Also note that this formula requires B31:B41 to be empty. Let us know if this is closer to what you want.

 

[jasonb75]

With the table below copied to A1:H16 of a blank sheet, try this formula in A3
=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($C$6:$C$16)/($C$6:$C$16=""),1)),"")
changing 1 to 2 for the 2nd place match, 3 for the 3rd and so on.
and this one in B3
=MATCH(A3,$B$6:$B$16,0)

Best Remaining QB	Overall		2nd Best Remaining QB	Overall		3rd Best Remaining QB	Overall
Rank	Rank	Rank
Deshaun Watson	2		Matt Ryan	4		Jared Goff	8
Rank	Player Name (QB)	Actual Sale ($$$)
1	Patrick Mahomes	$ 35
2	Deshaun Watson
3	Aaron Rodgers	$ 25
4	Matt Ryan
5	Carson Wentz	$ 15
6	Baker Mayfield	$ 35
7	Kirk Cousins	$ 45
8	Jared Goff
9	Dak Prescott
10	Russell Wilson
11	Cam Newton

<tbody>
</tbody>

 

[theBloodyNine]

Thank you all! The above thread by Jasonb75 is the one that did the trick for future users. Eric W formula did work also, however, when you update and add more prices to the list. Eric's formula did not change the names. This may absolutely be my fault, and not the formulas.

The formula Jasonb75 provided did allow for dynamic changes as you added more values (i.e. Matt Ryan for $25)


Thank you all!