Results 1 to 3 of 3

VLOOKUP array formula

Threaded 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.

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