theBloodyNine
New Member
- Joined
- Aug 30, 2019
- Messages
- 4
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.
=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.
