+ Reply to Thread
Results 1 to 13 of 13

Two Lookup and Return (Lookup the column, then lookup the row)

  1. #1
    Registered User
    Join Date
    05-14-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    92

    Two Lookup and Return (Lookup the column, then lookup the row)

    I need help to first look up the column name say "ONT 1" then the date say "8/04/2015", and return the matched column / row value. Please help, thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    Tried this?
    https://support.microsoft.com/en-us/kb/59482
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    Hi,
    Assuming you have the Date you search in I1 and the other value in J1, try this formula :

    Please Login or Register  to view this content.
    GC Excel - My blog

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  4. #4
    Registered User
    Join Date
    05-14-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    Thanks, I knew it was match / index, just have always had a problem with them for some reason...

  5. #5
    Registered User
    Join Date
    05-14-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    One more question, is there a way i still lookup both the column and row using match / index and then return the max in that particular column?

  6. #6
    Registered User
    Join Date
    05-14-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    So for the date, it would be the max variable while the column remains fixed?

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    Here's another one...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    ONT 1
    ONT 2
    ONT 3
    ONT 4
    ONT 5
    2
    8/5/2015
    54
    61
    64
    67
    76
    3
    8/4/2015
    10
    20
    30
    15
    45
    4
    8/3/2015
    60
    53
    68
    48
    25
    5
    8/1/2015
    15
    25
    35
    65
    84
    6
    7
    8
    9
    8/4/2015
    ONT 1
    10


    This formula entered in C9:

    =VLOOKUP(A9,A2:F5,MATCH(B9,A1:F1,0),0)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    Quote Originally Posted by Branbran10 View Post
    So for the date, it would be the max variable while the column remains fixed?
    In other words...

    Return the max value from column ONT 1?

  9. #9
    Registered User
    Join Date
    05-14-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    maybe i didnt explain properly. I need to look up "ONT 1" then find the max in that column, then subtract it by the max in "ONT 2", by looking up both "ONT 1" and "ONT 2". Preferably, I would also like to restrict this to the last 365 days.

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    Not sure what this means:

    subtract it by
    Does that mean max ONT 1 minus max ONT 2 or the other way around?

    restrict this to the last 365 days
    From when? Today?

  11. #11
    Registered User
    Join Date
    05-14-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    Yes and Yes

  12. #12
    Registered User
    Join Date
    05-14-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    Anything sir?

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Two Lookup and Return (Lookup the column, then lookup the row)

    Something like this...

    Array entered**:

    =MAX(IF(A2:A5>=TODAY()-365,B2:B5))-MAX(IF(A2:A5>=TODAY()-365,C2:C5))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

+ 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. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  2. Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  3. Lookup a blank cell in a Lookup table and return a figure.
    By jonnops in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-20-2014, 07:33 AM
  4. Replies: 1
    Last Post: 11-07-2012, 01:57 PM
  5. Replies: 5
    Last Post: 02-24-2011, 11:26 AM
  6. Lookup adjacent column to multiple lookup columns.
    By JAMES4228 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-13-2009, 03:19 PM
  7. Replies: 1
    Last Post: 01-19-2005, 09:06 PM

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