+ Reply to Thread
Results 1 to 4 of 4

Using A Formula To Lookup Data From Another Table Based On A Reference Column

  1. #1
    Registered User
    Join Date
    08-09-2017
    Location
    Wyoming, MI
    MS-Off Ver
    2017
    Posts
    3

    Using A Formula To Lookup Data From Another Table Based On A Reference Column

    Okay, so my title is probably as confusing to you as this quandary is to me.

    I have two tables, simplified for clarity. *See Attached Image* My full table 1 is 700 rows tall, and table two is 22 columns wide.

    Tables.jpg


    What I am looking to do is take data from Table #1 and display it in Table #2 in the "REF. WK" column based on the user associated with that row. If "REF. WK" equals 'Week 2' then I want the values 0,27,0 to display for the corresponding User reference.

    The only thing I can think to do is a VLOOKUP or an INDEX, but I can't quite seem to figure out how to tell my formula to display data based on what REF. WK I choose. A simple VLOOKUP or INDEX won't work, I believe, since it requires a static column row number (rather than reference).

    I've heard of a Structured Reference and Indirect Reference, but I am clueless how to use them - would either of those work for this problem that I have? Or does anyone have any other ideas as to how I could accomplish this variable display?
    Last edited by TheBaileyBrew; 08-09-2017 at 08:01 PM. Reason: SOLVED.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Using A Formula To Lookup Data From Another Table Based On A Reference Column

    In your example above...

    Table1 is A1:E4
    Table2 is G1:L4

    In H1 and copied down >> =HLOOKUP($H$1,$B$1:$E$4,ROW(A2),0)

    Note: If you wanted an Index/Match solution. In H1 and copied down >> =INDEX($B$2:$E$4,MATCH($G2,$A$2:$A$4,0),MATCH($H$1,$B$1:$E$1,0))
    Last edited by jeffreybrown; 08-09-2017 at 07:14 PM.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    08-09-2017
    Location
    Wyoming, MI
    MS-Off Ver
    2017
    Posts
    3

    Re: Using A Formula To Lookup Data From Another Table Based On A Reference Column

    That's Perfect!

    For some reason the HLOOKUP wasn't working, and I couldn't even make it work by swapping out VLOOKUP with a COLUMN reference in place of ROW.

    But the INDEX/MATCH solution worked like a charm once I started using the right table references.
    =INDEX('2017 TOTALS'!$A$3:$AM$227,MATCH('2017 COUNTS'!$H5,'2017 TOTALS'!$A$3:$A$250,0),MATCH('2017 COUNTS'!$A$3,'2017 TOTALS'!$A$2:$AM$2,0))


    Thank you so much! You just made my job so much easier when I go into work tomorrow morning.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Using A Formula To Lookup Data From Another Table Based On A Reference Column

    Wonderful news and you are very 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. IF formula- how to reference a table column
    By Excelguy928 in forum Excel General
    Replies: 2
    Last Post: 05-22-2014, 01:28 PM
  2. Replies: 12
    Last Post: 03-07-2014, 08:55 AM
  3. [SOLVED] fill reference table based on data available in master Table
    By jsimha in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-31-2013, 12:19 PM
  4. [SOLVED] Create Dynamic Column Lookup Reference based on Table Header
    By jeversf in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2013, 12:49 PM
  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. Function to split up a table based upon the values in a reference column
    By alexdragne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2012, 02:45 PM
  7. Replies: 3
    Last Post: 03-15-2006, 10:35 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