+ Reply to Thread
Results 1 to 5 of 5

Identifying a row number by finding a value across multiple columns and multiple rows

  1. #1
    Registered User
    Join Date
    02-28-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Identifying a row number by finding a value across multiple columns and multiple rows

    I have a table where I want to pull the value in Column B on the row in which the data I am looking up is located.

    A - B - C - D - E
    xyz - x - x - x - x
    def - x - y - x - x
    ghi - x - x - x - x

    In this example i want to create a formula that will result in 'def' because of where the y (in this case column 'c') knowing that the y could show up anywhere in the table B - E

    My original thought was =index(A:A,match('y',B:E,0)) but realized i can not do the array portion of the match function across multiple columns. Is there another way of doing this?

    Thanks.

    Josh

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Identifying a row number by finding a value across multiple columns and multiple rows

    hi Josh, welcome to the forum. assuming data in A2:E4, try this array formula:
    =INDEX(A2:A4,MAX((B2:E4="y")*(ROW(B2:E4)-ROW(B2)+1)))

    you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it
    or if you prefer non-array:
    =INDEX(A2:A4,INDEX(MAX((B2:E4="y")*(ROW(B2:E4)-ROW(B2)+1)),))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    02-28-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Identifying a row number by finding a value across multiple columns and multiple rows

    Yes! BUT, when i copied the cell down to a new row to pull in a difference reference value (instead of 'y') it didn't work right. I have attached what I am working in. On the Employee Schedule Tab I am trying to use this formula to populate the client an individual will be working on based on the date in the "Client Schedule" Tab. As you can see, Josh's pulls forward perfectly. However when i use the same formula to reference John+date (which should result in an error for 1/7/13 because John is not yet assigned to anything that week) it still populates with a client name.

    Thank you so much for your help too btw. I love learning new things that I can do with excel.
    Attached Files Attached Files

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Identifying a row number by finding a value across multiple columns and multiple rows

    with your first eg, i assumed there's always a "y". now that it's a possible scenario without the combination of Name & Date, then try this in D4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or non-array:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    hope that helps.

  5. #5
    Registered User
    Join Date
    02-28-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Identifying a row number by finding a value across multiple columns and multiple rows

    Thats works great! Thanks for your help!

+ 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