+ Reply to Thread
Results 1 to 6 of 6

Complex Function: search vertical column list to correspond numbers from another list

  1. #1
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    15.40
    Posts
    3

    Complex Function: search vertical column list to correspond numbers from another list

    Im running version 15.40

    Let me try to simplify the function I need.

    Imagine a vertical column of 500 random 3-digit numbers. So something like this...

    123
    231
    354
    394
    457
    534
    543
    644
    744
    873
    ... etc (500 data points in total)


    I also have a smaller vertical column of 3-digit numbers (say 35 numbers in total) that correspond to some of the numbers in the larger set of 500 numbers. These 35 numbers come with additional data in 3 columns beside it. Looks something like this...

    123...apple...blue...tree
    354...orange...red...bush
    457...banana...green...twig
    644...pear...purple...grass

    Note that the numbered column is in order from lowest to highest.

    What I need to do is this: I need a function that will allow me to in one go match the smaller list of 3-digit numbers with the data beside it with its corresponding twin (the same 3-digit number) in the long sequence of 500 3-digit numbers, so that the sequence of 500 numbers is not affected but the data that goes with the smaller list is plugged into the larger sequence. After the function it will look like this...


    123...123...apple...blue...tree
    231
    354...354...orange...red...bush
    394
    457...457...banana...green...twig
    534
    543
    644...644...pear...purple...grass
    744
    873
    etc to 500 data points


    Basically, i need a function that will search the larger list and find the corresponding number and then attach the data from the smaller list with it with leaving the gaps. This function will save me days of work.


    If there is no function like this then can you please suggest something?

    Thank you.
    Last edited by Jeremy Forsythe; 12-06-2018 at 07:23 PM.

  2. #2
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    15.40
    Posts
    3

    Re: Complex Function: search vertical column list to correspond numbers from another list

    Here is the attachment that demonstrates the data before the function and the data after the function.
    Attached Files Attached Files

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2007
    Posts
    8,582

    Re: Complex Function: search vertical column list to correspond numbers from another list

    Hi Jeremey,

    I'm not entirely sure what the relationship is between B3:B17 and then what's in F3:F7, but it D22 copied to the right and down...

    =IFERROR(VLOOKUP($C22,$F$3:$I$7,COLUMN(B$1),0),"")
    HTH
    Regards, Jeff

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2007
    Posts
    8,582

    Re: Complex Function: search vertical column list to correspond numbers from another list

    Or are you saying you want the results in C22:F36 in C3:F17?

    If so, in C3 copied down >> =IF(ISNUMBER(MATCH($B3,$J$3:$J$7,0)),$B3,"")
    In D3 copied to the right and down >> =IFERROR(VLOOKUP($C3,$J$3:$M$7,COLUMN(B$1),0),"")

  5. #5
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    15.40
    Posts
    3

    Re: Complex Function: search vertical column list to correspond numbers from another list

    Perfect thank you! Worked perfectly!

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2007
    Posts
    8,582

    Re: Complex Function: search vertical column list to correspond numbers from another list

    You are very welcome and thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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