+ Reply to Thread
Results 1 to 7 of 7

Lookup using relative reference

  1. #1
    Registered User
    Join Date
    09-28-2015
    Location
    Pasig, Philippines
    MS-Off Ver
    2013
    Posts
    3

    Lookup using relative reference

    Hi everyone!

    I have a problem using VLOOKUP...

    For the Meow rows, I want to return the number of Cat (which is 1 or 4 or ... depending on which is above it).
    The data is arranged as shown below..

    1 Cat
    2 Meow 1
    3 Meow 1
    4 Cat
    5 Meow 4
    6 Meow 4


    What I need for a function to do is to: LOOKUP the number of the cat that is NEAREST ABOVE the meow.
    Hope you guys could help. Thanks!!

  2. #2
    Forum Contributor
    Join Date
    08-07-2015
    Location
    AB, Canada
    MS-Off Ver
    Excel 2013 x64
    Posts
    132

    Re: Lookup using relative reference

    The following is a solution that uses ARRAY formulas. The formula must be entered by pressing Ctrl+Shift+Enter instead of just Enter.

    Assuming your numbers start in cell A1 and continue down column A, and your Cats and Meows start in B1 and continue down column B, the following ARRAY formula in cell C1 and dragged down column C will give you your desired result.

    Please Login or Register  to view this content.
    If the formula is returning 0's and blanks, you haven't entered the formula using Ctrl+Shift+Enter. You'll know you have done it correctly when you see the curly brackets around the entire formula {}.

    Let me know if that helps!

    Cats.png
    Last edited by ThirdFret; 09-29-2015 at 01:44 PM.

  3. #3
    Registered User
    Join Date
    09-28-2015
    Location
    Pasig, Philippines
    MS-Off Ver
    2013
    Posts
    3

    Re: Lookup using relative reference

    Hi ThirdFret! Thanks for the response! However, the numbers of the cats are actually huge randomized numbers, so I can't use the "LARGE" function.

    Here's a better example:
    987654321 Cats
    1 Meow 987654321
    2 Meow 987654321
    789 Cats
    26 Meow 789
    36 Meow 789

    What I want the function to do: Go to the left of Meow and Lookup the cat number nearest above it.
    So far, there's OFFSET and LOOKUP. But I don't know how to combine them. Or maybe there are other ways.
    Hope you guys could help. Thanks!

  4. #4
    Registered User
    Join Date
    09-28-2015
    Location
    Pasig, Philippines
    MS-Off Ver
    2013
    Posts
    3

    Re: Lookup using relative reference

    Hi again ThirdFret! Really grateful for your reply!
    I already figured it out using your formula and some index-match!!! Yey. Thank you so much. :D
    Here's my output below:

    Cats.png

    To those who have the same problem,
    first, number your cats in a separate column, say column A
    second, use the formula on column D: =IF(B1="Cat","",LARGE(IF($B$1:B1="Cat",$A$1:A1,0),1))
    third, use index-match to get the randomized numbers on column E: =IFERROR(INDEX($C:$C, MATCH($D1,$A:$A, 0)), "")

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,139

    Re: Lookup using relative reference

    D1=IF(B1="Cat","",LOOKUP(2,1/(B$1:B1="Cat"),C$1:C1))
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,139

    Re: Lookup using relative reference

    or
    D2=IF(B2="Cat","",IF(B2=B1,D1,C1))
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    08-07-2015
    Location
    AB, Canada
    MS-Off Ver
    Excel 2013 x64
    Posts
    132

    Re: Lookup using relative reference

    Quote Originally Posted by renospread View Post
    Hi again ThirdFret! Really grateful for your reply!
    I already figured it out using your formula and some index-match!!! Yey. Thank you so much. :D
    Glad I could help! Thanks for posting your final solution to the forum.

    If you feel the original question has been answered you should mark the thread as SOLVED, under the Thread Tools menu in the top right.

    Cheers!

+ 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. Relative reference table lookup and adjustment
    By jherbert81 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-14-2014, 08:02 PM
  2. Replies: 5
    Last Post: 09-21-2012, 02:08 PM
  3. change from relative reference to absolute reference
    By ronlau123 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-13-2011, 04:57 AM
  4. Replies: 0
    Last Post: 11-15-2007, 02:35 AM
  5. relative reference
    By Swannybuck in forum Excel General
    Replies: 1
    Last Post: 07-26-2006, 04:30 PM
  6. Relative Reference
    By liseladele in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-28-2006, 03:17 PM
  7. [SOLVED] Relative Reference
    By Hari in forum Excel General
    Replies: 4
    Last Post: 10-25-2005, 09:05 PM

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