+ Reply to Thread
Results 1 to 3 of 3

What to do if more than 7 ifs are required, and Vlookup will not serve purpose?

  1. #1
    Registered User
    Join Date
    07-15-2008
    Location
    Cambridge, UK
    Posts
    2

    What to do if more than 7 ifs are required, and Vlookup will not serve purpose?

    I have a table as follows:
    From To Bus fare (Rs.)
    (km) (km)
    0 2 1
    2 4 3
    4 6 4
    6 8 5
    8 10 6
    10 12 7
    12 14 7
    14 16 8
    16 18 8
    18 20 9
    20 22 9
    22 24 10
    24 26 10
    26 28 11
    28 30 11
    30 32 11
    32 34 12
    34 36 12
    36 38 12
    38 40 13

    The first to columns are km and the third column is bus fare. Now I have a column elsewhere (with about 400 rows) each of which contains a distance value. Baesd on the range, I would like to assign a fare. Eg, if the distance is 5.68km then fare is Rs.4, if distance is 21.09km then fare is Rs.9, and so on. I tried the nested IF, but realised that not more than 7 can be had. I am not sure if VLOOKUP will work as the value being sough is from a range. Please any help to sort this out would be very useful.

    Bhargav
    PhD candidate
    Cambridge University, UK

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    How about?

    Assuming your table is in A1:C20 and assuming your first lookup mileage is in F1...

    Try:

    =LOOKUP(F1,$A$1:$A$20,$C$1:$C$20)

    or

    =VLOOKUP(F1,$A$1:$C$20,3,TRUE)

    adjust ranges/references to suit and copy down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    07-15-2008
    Location
    Cambridge, UK
    Posts
    2
    Dear Moderator,

    Many thanks for your prompt reply. Both your suggestions worked. I did give VLOOPUK a try earlier, but must have been entering arguments wrongly. Didn't really peruse it further as perhaps I underestimated the power of VLOOKUP. Sorry for impatiently jumping onto the forum.

    In the meantime I did it the long way by having 20 columns with separate formulas and then summing it up, and indeed it gives the exact answer!

    Thanks again,

    Bhargav

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1