+ Reply to Thread
Results 1 to 5 of 5

Look in 2D Named Range and return the Row/Column Number of a reference Cell Value

  1. #1
    Registered User
    Join Date
    10-09-2010
    Location
    Seku
    MS-Off Ver
    Excel 2003
    Posts
    27

    Look in 2D Named Range and return the Row/Column Number of a reference Cell Value

    Greetings,
    Even though it feels like an easy problem, I didn't manage to get an elegant solution for it.

    I have:
    A named range Table1=O3:AG15
    A List of Names as a named range List1= J31:J60 (Text Data)

    A Name(From List1) may appear in any cell within Table1 using some VBA code.
    I was trying to make a function that looks up each Name from List1 against the Table1 and if it exists to return the Row number at AY31:AY60 and the Column Number at AZ31:AZ60, else to return 0

    Any help would be appreciated and thank you in advance.-

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Look in 2D Named Range and return the Row/Column Number of a reference Cell Value

    Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-09-2010
    Location
    Seku
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Look in 2D Named Range and return the Row/Column Number of a reference Cell Value

    Here it is... Tried to be as thorough as possible.
    Attached Files Attached Files

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Look in 2D Named Range and return the Row/Column Number of a reference Cell Value

    Hi,

    First question.

    Is your table 1 layout the only way you can collect your basic data. i.e. is this provided from some other system/workbook? If not and the data is manually entered are you able/willing to record the data in a different layout which would make analysis much simpler?

    If not this is likely to need a macro.

  5. #5
    Registered User
    Join Date
    10-09-2010
    Location
    Seku
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Look in 2D Named Range and return the Row/Column Number of a reference Cell Value

    Unfortunately I have little mobility on that part. At the moment, I've "hard-listed" each cell that belongs to Table1 into a single column and then manually entered the appropriate values that each one should return. After that, a simple Vlookup of the Names against Table1 returns the correct data... Yet, inelegant,crude and most importantly immobile for future expansion and use. I was hoping for someone with deeper knowledge on arrays to give me a hand with expanding the Match formula from the single column it can normally handle... At least at determining the Row and Column numbers.

+ 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. Query date range and ref number match to return another reference number
    By 27paul0 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-09-2015, 07:31 AM
  2. [SOLVED] If cell contains text contained in a named range return the row number
    By spoursy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-05-2014, 05:54 AM
  3. [SOLVED] Creating Column Reference from Named Range
    By avr5iron in forum Excel General
    Replies: 1
    Last Post: 04-22-2014, 01:02 PM
  4. [SOLVED] Find specific cell and return column number from a dynamic range
    By nivoe in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-18-2012, 12:39 PM
  5. [SOLVED] Return number of rows used in a named range?
    By Traziness in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-25-2012, 10:33 AM
  6. Return the first cell reference from a named list
    By zinny in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-20-2008, 11:04 AM
  7. Possible to reference column of named range in array formula?
    By Kel Good in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-15-2005, 02:50 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