Thread: formula Index/Match

Ok See attached.

Basically, Evergreen Estates has the lowest pricing at $1085. Thus they are the "Best Bidding Supplier"

Now I need to populate a cell box for "Best Bid RFI Score"

Evergreen Estates RFI score is directly below their pricing. I just can't figure out how to get the best pricing company to have their RFI score displayed in the "Best Bid RFI Score" row. I'm guessing I need to use some type of Match and Index, but I can't figure it out.

Any help?

Thanks, but I realized I didn't fully explain myself

See the new attachment. What I need is the RFI numbers to go in the orange boxes with the ????????'s.

Also, I included my formulas I was currently using to calculate the other stuff.

Basically for "Best Bid RFI Score" I need it to look up "Best Bidding Supplier" and then I need it to display that best bidding suppliers RFI.

For example take column D. Evergreen Estates is the best bidder, because their pricing is the lowest. I already have a formula in D13 that displays their name. Now I need D17 to display Evergreen Estates RFI score which can be found in D38.

However, I would like to set this up so if I added in a new company named Company B, and their pricing was lower than Evergreen Estates pricing, I would like D17 to display Company B's RFI score now, and not Evergreen Estates.

Then I would like to drag that formula across for all the locations so it automatically finds the best bidders RFI score. I know right now some of the columns don't even have RFI scores, but I'll input those later.. so right now when you drag it across it will probably say "0" for a couple of the companies. I also would like to have a formula that finds the 2nd best bidders RFI score, which is where row 18 would go.

Any ideas?

Well I figured out how to get the highest RFI score for the best bidder!

I took

=INDEX($B$21:$C$48,MATCH($C$6,$A$21:$A$48,0)+1,2) and I just did
=INDEX($B$21:$C$48,MATCH($C$6,$A$21:$A$48,0)+1,3)
=INDEX($B$21:$C$48,MATCH($C$6,$A$21:$A$48,0)+1,4)
=INDEX($B$21:$C$48,MATCH($C$6,$A$21:$A$48,0)+1,5)
=INDEX($B$21:$C$48,MATCH($C$6,$A$21:$A$48,0)+1,6)
=INDEX($B$21:$C$48,MATCH($C$6,$A$21:$A$48,0)+1,7)

etc....

and that works!! Thank you so much Palmetto!!

Can anyone download Lawncare2.xlsx above and tell me how to find the value for the cell D19?

What it should do is count how many times Evergreen Estate put pricing in for their "Lawn" row.
So the answer for D19 should be 1.

In case you are confused here is what I need. See attached.

Thank you