+ Reply to Thread
Results 1 to 3 of 3

VLOOKUP array formula

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-05-2009
    Location
    Cologne, Germany
    MS-Off Ver
    Excel 2010
    Posts
    156

    VLOOKUP array formula

    I have the following information (small sample only):

    Code Item List
    162 AVS0001 AVS0001
    162 AVS0002 AVS0003
    162 AVS0003 BUD0002
    162 AVS0005 C5O1208
    102 BRE234 CAL0007
    102 BRE-508 EQU0003
    102 BRE-AB09 FXUK21916/2
    162 BUD0002 GMC0003
    162 C5ENV-TNT HSS0001
    162 C5O1208 HSS0006
    162 CAL0005 IBM0003
    162 CAL0007 KUN0002



    The list on the right is derived from the following array formula which has been copied down the column to end of the data range:

    {=VLOOKUP($G$130, INDEX(G133:I252, SMALL(IF($G$130=INDEX(G133:I252, , 1), ROW(G133:I252)-MIN(ROW(G133:I252))+1, ""), ROW(1:1)), , 1), 2, FALSE)}

    $G$130 refers to a value in that cell which is user defined from a drop down list and in this instance, the value is 162. Therefore, I would expect to see all of the items that contain 162, under the list column. As you can see, it has not returned all of the values relevant to 162. It has missed AVS002, AVS005 C5 ENV and CAL0005 in this example. The ommissions seem random. Any help would be appreciated!
    Last edited by kborgers; 01-29-2010 at 09:16 AM.

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

    Re: VLOOKUP array formula

    It would be nice to see a sampling of actual data (replacing confidential data)... but at first glance... should the range G133:I252 be absolute? Ie. have $ signs to "freeze" the ranges, so you can copy down without changing them?
    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
    Forum Contributor
    Join Date
    09-05-2009
    Location
    Cologne, Germany
    MS-Off Ver
    Excel 2010
    Posts
    156

    Re: VLOOKUP array formula

    Ahhh... the simplest suggestions are often the best! You are absolutely right. I froze the range, then copied down and now it seems to work fine - bit more testing to be sure but looks good so far - thanks!!

+ 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