Vlookup in VBA generating error

[JackDanIce]

Hi,

I'm trying to condense following:

    Select Case i
        Case 1 To 3:    ForeCastCol = "A"
        Case 4 To 10:   ForeCastCol = "B"
        Case 11 To 15:  ForeCastCol = "C"
        Case Else:      ForeCastCol = "D" 'Limit is 18 regardless
    End Select

To:

Application.Vlookup(i, [3, 10, 15, 18], ["A", "B", "C", "D"], True)

Where "A", "B" etc are irrelevant or an equivalent function

Limits of 3, 10, 15 and 18 will never change (and always ascending order) can someone suggest?

TIA,
Jack

 

[Fluff]

Try

    ForeCastCol = Application.Lookup(i, Array(0, 4, 11, 16), Array("Total_", "Practices_", "Central_", "AffL_"))

But I'd agree with shg & stick with the select case

 

[JackDanIce]

Cheers @Fluff looks like my limit values needed to change, but yes I went back to Select Case after @shg replied was curious as to what the error meant or cause of it, incorrect limit values.

 

[JoeMo]

It is @shg? If I revert to that, then won't have these errors...

Thanks for the comment @shg, curious to know why this doesn't work now, but if Select Case is faster (and working) then, will revert.

The lookup will fail if i is less than 3.

 

[JackDanIce]

Thanks @JoeMo I do need to account for all values 1 - 18, despite buckets - useful to know for next time