+ Reply to Thread
Results 1 to 4 of 4

Return cell address from 2D lookup

  1. #1
    Registered User
    Join Date
    12-08-2011
    Location
    Newfoundland, Canada
    MS-Off Ver
    Excel 2013
    Posts
    10

    Return cell address from 2D lookup

    I am trying to find a way to return the cell address for a given value in a range (multiple rows and columns).

    For example, in the range A1:D5 are numbers 1 to 20, not in any particular order and no duplicate values.

    15,5,1,11
    14,16,4,2
    13,6,20,18
    9,3,12,8
    10,19,7,17

    I want (preferably a formula) to return the address of the cell that contains a given number (e.g. 12 would return C4 or 17 would return D5).

    Is it possible?

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Return cell address from 2D lookup

    a
    b
    c
    d
    e
    f
    g
    h
    1
    15
    5
    1
    11
    12
    c4 g1: =address(sumproduct(($a$1:$d$5=f1) * row($a$1:$d$5)), sumproduct((a1:d5=f1) * column($a$1:$d$5) ), 4)
    2
    14
    16
    4
    2
    17
    d5
    3
    13
    6
    20
    18
    4
    9
    3
    12
    8
    5
    10
    19
    7
    17
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-08-2011
    Location
    Newfoundland, Canada
    MS-Off Ver
    Excel 2013
    Posts
    10

    Thumbs up Re: Return cell address from 2D lookup

    Freaking awesome! Thanks!

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Return cell address from 2D lookup

    You're welcome.

+ 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. V Lookup and return cell address
    By Thomas in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-30-2006, 04:15 PM
  2. [SOLVED] LOOKUP & RETURN CELL ADDRESS
    By Aladin Akyurek in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 07:05 AM
  3. LOOKUP & RETURN CELL ADDRESS
    By Duke Carey in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2005, 06:05 AM
  4. [SOLVED] LOOKUP & RETURN CELL ADDRESS
    By Carolan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  5. LOOKUP & RETURN CELL ADDRESS
    By Carolan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. LOOKUP & RETURN CELL ADDRESS
    By Carolan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] LOOKUP & RETURN CELL ADDRESS
    By Carolan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-05-2005, 11:05 PM
  8. LOOKUP & RETURN CELL ADDRESS
    By Carolan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-09-2005, 04:05 AM

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