VLookup Is Not Corresponding To Table

  • I'm somewhat confused on why VLookup is not extracting the correct amount from the table. I'm trying to attract the correct percentage from the remittance table. I've shaded in yellow what VLookup is extracting on the spreadsheet attached -- for some reason, if I change a percentage for AB, it affects the rest of the province's numbers.


    Any explanation for this occurance would be appreciated.


    Thanks,
    Hoberz

  • Re: VLookup Is Not Corresponding To Table


    only had a quick look, but shouldn't your provinces in column G be sorted A-Z to make the Vlookup work properly? try that and see

  • Re: VLookup Is Not Corresponding To Table


    Quote from tommymacca;557865

    only had a quick look, but shouldn't your provinces in column G be sorted A-Z to make the Vlookup work properly? try that and see



    Gotcha, I didn't know the table array had to be sorted in order.


    Thanks!

  • Re: VLookup Is Not Corresponding To Table


    mmmm.....


    I don't think sorting is the answer. One problem with that approach is that you can get "false matches" for codes that don't exist, e.g. what result do you get if you put "ZZ" in B20?


    For this sort of situation I assume that only an exact match makes sense.......so the 4th argument of VLOOKUP needs to be included, i.e.


    =VLOOKUP($B20,Rates,2,FALSE)


    With this version the table order is unimportant, it can be unsorted. Now if B20 is "ZZ" the formula returns #N/A

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!