+ Reply to Thread
Results 1 to 6 of 6

Return a value for finding range of cells in another range of cells

  1. #1
    Registered User
    Join Date
    01-14-2016
    Location
    Celje, Slovenia
    MS-Off Ver
    Office 365
    Posts
    3

    Return a value for finding range of cells in another range of cells

    Hi everybody.

    I'm doing an assignment for my work and have come to a problem I don't know how to solve.

    I have a table where the first row contains a number of the column and under that number there are 8 digits in another 8 rows (code for binary digits). Every next column containes the next code for binary digits (again in 8 rows). Under the data, I have another column with 8 rows, where I will randomly input the combination of binary code digits and would in the end like that Excel returns the number of the column from upper (main) table. Thus finding a range of cells in another range of cells.

    I'm attaching the Excel file to help with the "imaging" of the problem.

    Could this be done with INDEX + something else, or will I need programming?

    Should there be any additional questions or misunderstanding about my wishes, please don't hesitate to ask.

    Thank you for your help in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Return a value for finding range of cells in another range of cells

    Using your sample workbook, try this:
    Please Login or Register  to view this content.
    I was hoping for something more elegant, but Excel does a poor job of concatenating ranges

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,379

    Re: Return a value for finding range of cells in another range of cells

    Hi kuzner and welcome to the forum,

    I'd love to understand your problem so you get the answer you want. In B11 you show a 2. How did you get that? Are you doing binary addition? Are you simply showing how many non-zeros there are in a row or column?

    If I can understand the problem, I'm a lot closer to the answer....
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,379

    Re: Return a value for finding range of cells in another range of cells

    Now that Ron has understood the problem (thanks Ron)

    Try in B11
    =DECIMAL(VALUE(B12&B13&B14&B15&B16&B17&B18&B19),2)
    This will work in your example as you have counted upward..

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,379

    Re: Return a value for finding range of cells in another range of cells

    If you use Row 10 to convert those binary to decimal values you might use this equation.

    =MATCH(DECIMAL(VALUE(B2&B3&B4&B5&B6&B7&B8&B9),2),$A$10:$H$10,0)

    See the attached for my examples above.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-14-2016
    Location
    Celje, Slovenia
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Return a value for finding range of cells in another range of cells

    Hello Ron and Marvin.

    The last solution you provided Marvin, using only Match function, worked like a charm. In the process I also learned a new function Decimal, therefore win win situation.

    Thank you guys. I'm definitely coming back, should there be some new problems.

    Best regards, MArko.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Finding a value in a range of cells
    By imike964 in forum Excel General
    Replies: 8
    Last Post: 07-09-2014, 12:22 PM
  2. [SOLVED] Return non-blank cells from range AND ignore cells with only formula w/o results
    By grafitti in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-06-2014, 08:15 AM
  3. Replies: 1
    Last Post: 09-04-2013, 07:23 AM
  4. Finding range of Cells containing low (range) values....
    By kickme in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-11-2012, 02:56 AM
  5. Replies: 5
    Last Post: 08-29-2012, 03:53 AM
  6. Replies: 3
    Last Post: 04-01-2012, 05:37 AM
  7. [SOLVED] Return Sum of Range 11 cells to left of another range
    By jrm0523 in forum Excel General
    Replies: 2
    Last Post: 03-28-2012, 10:04 PM

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