Results 1 to 3 of 3

Thread: Need help in look up / V look up

  1. #1

    Exclamation Need help in look up / V look up

    Register for a FREE account, and/
    or Log in to avoid these ads!

    The sample excel enclosed contains formulas for calculation of Ascendant at 9:00 AM current date at a specific time zone / latitude / longitude.

    However, I need to calculate in just reverse way. I mean at what time, the ascendant will be 0 degree / 30 degree / 60 degree...etc. (for same date)

    So, the Ascendant values every minute using same formula.(in Data sheet)

    However, in Result sheet I have 2 problems still needing some help from experts.

    1. With LOOKUP function used in Result! C5 & C11, it stuck at the end of range..i. e. at 360...I should consider AG3-AH3 range again after AG14-AH14...

    2. With VLOOKUP function in C6 & C12, it should show the 'Time' where the nearest value in DATA!V matches with Result!C5 , C11

    Can someone suggest the correct formulas please ?

    Thanking in advance.
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    York, England
    Excel Version
    I don't pretend to understand what your trying to do, but I can help a little by pointing out what your current formulae are doing.
    1. VLOOKUP (C6) Takes the value in C5, and finds a value in V3:V903 that is the largest number thats smaller than C5, and it then returns the time in col X on the same row. There is a big catch though.. If you use TRUE as a parameter, then the entries in col V must be in numerical order. To illustrate this if you change the lookup array to V3:V289 it will return 10:36:00.

    2. LOOKUP (J11) Here the formula is taking the 360 fom I11 and returning the value from column AE that corresponds with the largest value in AD that isn't greater than the 360 (ie 360 again). In other words because you have reached the end of the data in column AD, the last value will be chosen each time if the look up value is greater than the last array value (assumes they are in ascending order).


  3. #3
    Conjurer Beamer's Avatar
    Join Date
    Nov 2014
    Excel Version
    Hi Chaitreya, taking into consideration what Hercules1946 has said about your "TRUE" lookup needing your lookup column to be in numerical order, I've added formulas in columns Z and AA to sort this for you.
    Keep in mind, as Hercules1946 said, this will return the largest number "UP TO" the value searched for and not over. This is why my results are slightly out from yours.
    I hope this helps you.
    Attached Files Attached Files

Posting Permissions

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