+ Reply to Thread
Results 1 to 5 of 5

Search an area for a matching value and return the data from 2 cells above

  1. #1
    Registered User
    Join Date
    08-19-2018
    Location
    Seattle, wa
    MS-Off Ver
    Office 2010
    Posts
    2

    Question Search an area for a matching value and return the data from 2 cells above

    My area is A1:M60. I have 10 sets of 6 rows within that area with only 2 row from each set actually being searched (its ok to search entire area though as there will never be a match in the other rows). I am trying to search the area for the first instance of a reference cell and return the value of the cell that is 2 cells above it. I thought I could do it with an OR() and a series of HLOOKUPs but that didn't work. The result is always "0". Also, it looked really bulky for this function. Here is the function I have now:

    =OR(HLOOKUP(LEFT(Y3, 4),$1:$3, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$7:$9, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$13:$15, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$19:$21, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$25:$27, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$31:$33, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$37:$39, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$43:$45, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$49:$51, 1, FALSE))

    Any help would be greatly appreciated.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Search an area for a matching value and return the data from 2 cells above

    This regular formula returns the cell that is two above the first cell that equals the left-4-chars of Y3.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: Search an area for a matching value and return the data from 2 cells above

    If we take the first part of your formula, i.e.:

    HLOOKUP(LEFT(Y3, 4),$1:$3, 1, FALSE)

    this will look at row 1 to see if there is a match with the first 4 characters of Y3, and if so it will return the corresponding value from the same row (because of the 1 parameter). If you set this to 3 it would return the corresponding value from row 3, i.e. two rows BELOW the row that you are searching. From your description, it would appear that you want to do the opposite, i.e. search through row 3 and return a value from row 1 if there is a match, but HLOOKUP does not work that way. The same comments apply to your other HLOOKUPs.

    I'm not sure what your HLOOKUP is meant to return (text or numbers), but the OR function will return the equivalent of TRUE or FALSE, so I don't think that is what you want to do either.

    Perhaps you should think about using COUNTIFS to see if the value exists in row 3, like this:

    =IF(COUNTIFS($3:$3,LEFT(Y3,4)), … ,"")

    where … is some other function to return the appropriate value if there is a match, probably based on INDEX/MATCH, but as I'm not sure exactly what you want to achieve it is difficult to advise. It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as this does not work on this forum.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    08-19-2018
    Location
    Seattle, wa
    MS-Off Ver
    Office 2010
    Posts
    2

    Re: Search an area for a matching value and return the data from 2 cells above

    I tried it, but still got a #NUM! error. I am not very familiar with INDEX and have never used aggregate, so I will have to look into those more to trouble shoot. I will post the file at the end of this discussion and let you guys see what I am trying to do.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: Search an area for a matching value and return the data from 2 cells above

    I think you forgot the attachment.

    Pete

+ 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. [SOLVED] How to return the data in a row matching search criteria
    By Glio in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2018, 05:15 AM
  2. VBA to search for number and return in specific area of spreadsheet
    By DesertRose in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-19-2017, 04:48 PM
  3. [SOLVED] Search for String in Range, If Match, Return Matching Cell Value
    By ryanb909 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-21-2013, 02:27 AM
  4. Replies: 10
    Last Post: 05-21-2013, 12:58 AM
  5. Search Range for matching value return and to active cell.
    By morbdetro in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-02-2013, 03:08 PM
  6. Search Range for matching value return and to active cell
    By morbdetro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-01-2013, 11:43 AM
  7. Search to find matching cells and copy/transpose adjacent data to original sheet?
    By thump4r in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-12-2009, 09:20 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