Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Putnam Valley, New York, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup/Array (2002/XP)

    I'm trying to wrap my mind around Lookup functions and arrays. Our official accounting system doesn't provide details I need to project my budgetary needs, so I've built this spreadsheet (sample uploaded). Each item needs to belong to a category, but several categories share the same budget code. I would like to choose a category, and have Excel lookup the budget code. I've successfully made a validation list for my categories but I'm not sure to incorporate the lookup function.

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lookup/Array (2002/XP)

    You can use INDEX/MATCH combination

    Cell A3 =INDEX($L$3:$N$44,MATCH(B3,$N$3:$N$44,0),1)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Putnam Valley, New York, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup/Array (2002/XP)

    I did rearrange (sorted my categories alphabetically) so my vlookup now works. Thanks Tony for your speedy response, though.
    May I assume, Legare, that your IF statement is to avoid a "N/A" message for those rows that have not yet had a category selected? If you get a chance, please do attach the file.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup/Array (2002/XP)

    If you don't mind rearranging your lookup table a little so that the lookup value is in the first column (see the attached file), then you could use a formula like this:

    <pre>=IF(B3<>"",VLOOKUP(B3,$L$3:$N$44,2,FALSE)," ")
    </pre>

    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup/Array (2002/XP)

    Yes, the IF avoids the error if the category has not been selected.

    I attached the file to my original post. Sorry about that.
    Legare Coleman

Posting Permissions

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