Finding the next available supplier based on date last accepted and if available

[zefrogi]

I have a list of suppliers that goes on a rolling basis. I'd like to be able to know which suppliers are next in line based on when they last accepted as it should go the longest waiting and if they are available.

Supplier	Status	Date of Last Accepted
Test 1	Available	31/8/22
Test 2	Suspended	31/8/22
Test 3	Available	2/9/22
Test 4	Available	29/8/22

So I have 6 suppliers and I'd like to know who is next in line, but also the next supplier in line after that.

I have the code:

INDEX(SuppTable[Supplier],MATCH(SMALL(IF(SuppTable[Status]="Available",SuppTable[Date of last contract Accepted]),1),SuppTable[Date of last contract Accepted],0))

But I'm noticing an issues; if the suppliers have the same date as accepted it will only return first supplier of that date and it will include suspended suppliers.

Ideally, I'd like to have cells under the table that would read "First: Test 4, Second: Test 1, Third: Test 3". I feel like I'm missing someone major but can't quite put my finger on. I've recently moved to Office365 so getting used to that as well

 

[Snakehips]

@zefrogi As you have 365, how about using FILTER and SORT functions similar to below?

RandomXl.xlsx
	A	B	C
1	Supplier	Status	Date of Last Accepted
2	Test 1	Available	8/31/22
3	Test 2	Suspended	8/31/22
4	Test 3	Available	9/2/22
5	Test 4	Available	8/29/22
6	Test 5	Suspended	8/29/22
7	Test 6	Available	8/27/22
8
9
10	First:	Test 6
11	Second:	Test 4
12	Third	Test 1
13
Sheet11

Cell Formulas
Range	Formula
B10:B12	B10	=INDEX(SORT(FILTER($A$2:$C$7,$B$2:$B$7="Available"),3,1),ROWS(B$10:B10),1)

Hope that helps.

 

[zefrogi]

@zefrogi As you have 365, how about using FILTER and SORT functions similar to below?

RandomXl.xlsx
	A	B	C
1	Supplier	Status	Date of Last Accepted
2	Test 1	Available	8/31/22
3	Test 2	Suspended	8/31/22
4	Test 3	Available	9/2/22
5	Test 4	Available	8/29/22
6	Test 5	Suspended	8/29/22
7	Test 6	Available	8/27/22
8
9
10	First:	Test 6
11	Second:	Test 4
12	Third	Test 1
13
Sheet11

Cell Formulas
Range	Formula
B10:B12	B10	=INDEX(SORT(FILTER($A$2:$C$7,$B$2:$B$7="Available"),3,1),ROWS(B$10:B10),1)

Hope that helps.

Superb work Snakehips! Thanks for your solution. Appreciate you.