+ Reply to Thread
Results 1 to 8 of 8

Lookup Row Number based on known Column Number

  1. #1
    Registered User
    Join Date
    02-12-2020
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    3

    Lookup Row Number based on known Column Number

    Hi all,

    I'm in a really tricky spot with a document I'm doing. I work for an engineering company and I'm looking at automating some data pulls from Bills of Materials. I can make it do absolutely everything I need it to, bar find the row number I need.

    I can find the given column number using MATCH.

    I am then looking for a row by searching down that column, and telling me which row the Number 1 appears in.

    Example.PNG

    For example. If I picked "Red". It would tell me Column 3 (which I can find), and then because it's looking for Item Number 1, it would throw back "Row 2". This is because it would search for the Number 1 down Column 3. If I was looking for Green, it would tell me Column 5 and Row 4. If I was looking for Item 4 for Yellow, it would tell me Column 6 and Row 13. I need this to be automated based on which Colour you pick (I'd use data validation etc. to give us a drop down, and from that it would populate a pick list for everything we need)

    As I said, finding the column is the easy part. If I can find the row I can then use Index to let me find the part names.

    Is this something that can be done easily? I've been struggling with this for a while. Seems like it should be easy. I can do it with a large "IF" function, but would rather it be much simpler than that.

    Thanks.

    EDIT: Added example Spreadsheet. In this Spreadsheet I've wrote in the Text next to Item 1, 2, 3, 4, 5 and 6 that I would like it to automatically find by using the drop down. I think the way I've got the data presented might make this doable. If I need to change the way the data is presented let me know too and I can change that probably.
    Attached Files Attached Files
    Last edited by Adb123; 02-13-2020 at 06:20 AM. Reason: Added Example Spreadsheet

  2. #2
    Forum Contributor shank_mis's Avatar
    Join Date
    09-08-2018
    Location
    Delhi
    MS-Off Ver
    2010
    Posts
    128

    Re: Lookup Row Number based on known Column Number

    Let's assume your data is in column A to Column H and Row 2 to Row 17.
    Row 1 is fixed for header.

    Place your data validation in J1 & J2.
    J1 for Column number C1:H1
    J2 for Row Number B2:B17

    Now in Cell J3: ="Column "&MATCH(J1,A1:H1,0)&" and Row "&MATCH(J2,B1:B17,0)
    And...you are done.
    Shashank Mishra
    Please hit "Add Reputation" Button if you liked the answer.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,191

    Re: Lookup Row Number based on known Column Number

    It would be better if you attached a sample Excel workbook, rather than a picture of one. That way we would be able to see which columns you are using for your data and cells where you want to specify the colour and value. See the yellow banner at the top of the screen for details of how to do this.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    02-12-2020
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Lookup Row Number based on known Column Number

    Hi Pete,

    Thanks. I've now updated the original post with the example spreadsheet and a little bit more information on what I'm looking for.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,531

    Re: Lookup Row Number based on known Column Number

    Administrative note
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
    Thanks

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,191

    Re: Lookup Row Number based on known Column Number

    You can put this formula in cell M6:

    =IFERROR(INDEX($B$3:$B$18,MATCH(ROWS($1:1),INDEX($A$3:$H$18,0,$M$5),0)),"")

    and then copy it down. If you want to pick up the final two items on your list, then for completeness you should fill cells C17:H17 with 5 and C18:H18 with 6 (as on your screen shot), or use a different formula for those two cells (could be =B17 in cell M10).

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    02-12-2020
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Lookup Row Number based on known Column Number

    Hi Pete,

    That worked perfectly. Thank you. And yes, I for some reason didn't do 5 and 6 on the spreadsheet. I'm unsure why.

    Again, thank you for this. I'll be able to complete my spreadsheet now.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,191

    Re: Lookup Row Number based on known Column Number

    You're welcome - glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. [SOLVED] Using MATCH to find row number based on a two-column lookup.
    By systemlordanubis in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-17-2016, 08:34 AM
  2. Replies: 3
    Last Post: 05-08-2015, 12:05 PM
  3. Match Column A's Project number to change based on greater number in new row
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-02-2013, 10:18 AM
  4. [SOLVED] Count the number of unigue numbers based on number in another column.
    By ksmith4809 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-27-2013, 09:11 AM
  5. Lookup Column number in a table based on a cell value
    By electricmice in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-22-2012, 01:08 AM
  6. Lookup largest number in a column treating negative number as postive number
    By xWiZardx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2010, 08:09 AM
  7. Add Random Number in a Column based on defined Number in another Column
    By Pack Dog in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2010, 05:09 AM

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