Comparing vendor RFPs

I have a collection of data for multiple vendors with one column on the end for a manual input of the winning vendor. Presumably all vendors will bid on all projects, so I will have F3 through U41 all filled out with column V the manual plug.

My question is, on another tab, is there a formula that combines IF, INDEX, MATCH, etc, or would I just have to nest all of these to return what I want?

And further, I can't see how I would need to write this to not have blank spaces in it.

I have a summary tab that I have thrown in a drop down list for all of the vendors.

What I want to do in words is this:

When the menu is filtered to display Vendor X, I want cells D8:D(however many projects there are) to go lookup if there is a value in that respective vendor's listing of bids provided. The way it is set up, a VLOOKUP won't work. Then in the adjacent column, I want to perform the same type of lookup, but instead vendor specific, I want it to return the lowest bid offered so I can compare the two.

Does any of this make sense?

Thanks