Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    double lookup (excel 97)

    I'm trying to do a double lookup incorporating an age (1 to 24 months in whole months) and 6 test scores that lookup converts into scaled scores from 24 monthly tables of 7 columns (the 6 test scores and the scaled score equivalent). Then use that result to convert into a percentile and Developmental level.

    I planned to do this by making a lookup table converting the age into a set of lookup-vectors for each of the 6 test scores and naming those lookup cells and using the names in another lookup to convert a test score into a scaled score. Unfortunately the result I get is #N/A.
    I then tried to do it by naming the each of the columns in the 24 monthly tables (only got to 12 months so far), creating a lookup table converting the age into a set of names for each of the 6 test scores and naming those lookup cells and using the names in another lookup to convert a test score into a scaled score. Unfortunately the result I get is still #N/A.

    Sample file simplified to 1 raw score and 1-6 months age to get under 100kb.
    Any help with this double lookup plan appreciated.
    iicae

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: double lookup (excel 97)

    Your workbook is problematic: there are named ranges and formulas with invalid references #REF, and the first not-working formula (in F7) tries to look up a value in a range consisting of one cell.

    By the way, you can reduce the size of the workbook by clearing unused rows and columns (when I did this, it went from 95 KB to 46 KB), and to post it, you can zip the workbook (46 KB compressed to 14 KB)

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: double lookup (excel 97)

    Hans,

    Thank you for your interest and advice.

    In my original workbook I get no #REF errors when I paste out my names list but Cell F7 is my core problem.
    I need some way to utilise the result of the lookup in c10 and d10 in the lookup formula in F7. Naming the cell didn't work, naming the lookup formula hasn't worked (but gave me a #VALUE error instead). When I manually insert the c10 and d10 values into F7 it works but I need it to work using the result of any value that the c10 and d10 lookups produce in response to changing b4 inorder for the double lookup to work (if it can?).

    Appreciately
    iicae

    If there is another (easy) way to do a double lookup I'd be interested in that but I am an excel novice.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: double lookup (excel 97)

    I think you need the INDIRECT function, since ss_a and ss_q refer to cells that contain the names of named ranges. The INDIRECT function can be used to refer to the ranges instead of to the names.

    =LOOKUP($B$7,INDIRECT(ss_a),INDIRECT(ss_q))

  5. #5
    New Lounger
    Join Date
    Jul 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: double lookup (excel 97)

    Thanks Hans,

    The Indirect function worked perfectly.

    Your advice was great.
    iicae

Posting Permissions

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