Hi all,
Another question on Excel. I'm trying to Rank pricing of items so I can find the cheapest bidder. My table is structured as follows
1.png
As you can see, its a file compiled of multiple supplier bids, where the item ID repeats. Suppliers can choose to submit a bid on whatever product they choose.
Next I do a ranking based on the following formula
=IF(D2>0,SUMPRODUCT((B:B=B2)*(D:D<D2))+1,"error")
2.png
As you can see, this results in an "error" notification if a supplier has not made a bid. Moving on, I create a simple lookup by concatenating the product ID and Rank
3.png
Then I create this table, to give me the cheapest bid (vlookup (product id - rank1)). Here is where things start to go wrong
4.png
As you can see, I get an 'error' bid for product 1, even though it has a valid bid
Supplier A has been flagged as the cheapest bid for item 1, even though supplier C has bid an identical price. Looking at the ranking, they have both been given the Rank "1" and Excel picked the first one. Is there a way to modify the ranking formula to give them Rank 1 & 2, even if its alphabetical? It's just that if I do a lookup for Rank 2, I won't get anything.
Hope that was somewhat clear. I've also attached the spreadsheet for clarification.
Problem.xlsx
Bookmarks