+ Reply to Thread
Results 1 to 2 of 2

How to lookup and return multiple values

  1. #1
    Registered User
    Join Date
    01-27-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    1

    How to lookup and return multiple values

    Row Column
    1 B F
    2 LAP 1 SAP LCoA
    3 34103 151150011
    4 54915 530855000
    5 34103 221000048

    Above is a brief extract from the table I am working with.

    Basically I am trying to search for the value in column B and return the relevant value from column F. However, as you can see, ‘34103’ in column B (B4 & B6) will return two results from column F (F4 & F6) and therefore (assume) rules out the use of vlookup?

    Looking around on the internet, it seems as though the Index, If & Small functions are used within an array to produce the results I am after. However, I have been unable to get this to work.

    My table properties are columns A:F and Rows 1:21801.

    Thanks for your help.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: How to lookup and return multiple values

    Before going down the route of expensive array formulae -- have you considered using a Pivot Table with row B as a Page Field ?

    If you won't use a PT (and you should have a good reason for not doing so ;-)) then I would advise given the vastness of your source range that you create a concatenation key on source sheet that will allow you to dispense with need for array... eg

    Source Sheet ! G3: =$B3&"#"&COUNTIF($B$1:$B3,$B3)
    copied down

    You can then just use a standard INDEX/MATCH in your table looking first for

    34103#1

    then next row looks for

    34103#2

    etc etc...

    This will be a far quicker approach to arrays.

    But to reiterate - Pivot Table quicker still and requires no additional cells / formulae.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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