Levels of Nesting more than 7

**Bigwilliewilcox** · 12-03-2010, 03:01 PM

I am looking for an easy way to make this work:

=IF(Av17="bid1",i17,IF(Av17="bid2",M17,IF(Av17="bid3",q17,IF(Av17="bid4",u17,IF(Av17="bid5",y17,IF(Av17="bid6",ac17,IF(Av17="bid7",ag17,IF(Av17="bid8",ak17,IF(Av17="bid9",am17,IF(Av17="bid10",as17,""))))))))))

We do bids for awarding products. Ideally what I'm trying to do is find a simple way (as it works with 7 vendors and below) to put into the the correct amount of money into the cell next to the winning vendor for the product, ie:

Winning Vendor Winning Bid
Bid1 17.30
Bid3 23.20

This way when I put in the winning vendor, it automatically puts in their bid amount.
Obviously i can't do this cause I have 10 vendors here, is there an easy way to make this work? I'm not sure I understand LOOKUP very well and I'm kind of a novice, obviously.

**NBVC** · 12-03-2010, 03:33 PM

Perhaps?

=INDEX($I17:$AS17,4*(SUBSTITUTE($AV17,"bid","")-1)+1)

**mrice** · 12-03-2010, 03:36 PM

Why not try a user defined function like this.

Function GetBidAmount(Bidder As Range)
Select Case Bidder
    Case Is = "bid1"
        GetBidAmount = Bidder.Offset(0, -39)
    Case Is = "bid3"
        GetBidAmount = Bidder.Offset(0, -35)
    'Add as many levels as you like

End Select
End Function

Paste this into a new module in the VBA editor (Alt F11) and then enter = GetBidAmount(AV17) in an appropriate cell.