+ Reply to Thread
Results 1 to 3 of 3

Matching specified range vs entire column

  1. #1
    Registered User
    Join Date
    06-27-2018
    Location
    Houston, Texas
    MS-Off Ver
    Office 365
    Posts
    2

    Matching specified range vs entire column

    I'm trying to use the match function to identity the row a value is in, however i am running into an issue. Because of the nature of my data, I want to specify the range in which i would like to make the match. This is where I am encountering my issue. If I use the entire column in my match function, I obtain the correct result. However when I try to specify a range that includes the correct value, I get the incorrect value.

    L908 = MAX(K120:K360) = 15.01 so I know this value exists in this range

    =MATCH(L908,K:K,0) returns 248 (correct) K248 = 15.01

    =MATCH(L908,K120:K360,0) returns 129 (incorrect) K129 = 5.3

    Is there something I am missing?

    Thanks.
    Last edited by Jshel28; 06-27-2018 at 02:25 PM.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Matching specified range vs entire column

    The match function returns not the row number, but the row number within your range. So... =MATCH(L908,K120:K360,0) returns 129 (incorrect) K129 = 5.3 returns 129 because the 129th row in 120:360 was matched.
    If you consider K120 Row "1" then the 129th row is K248.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    06-27-2018
    Location
    Houston, Texas
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Matching specified range vs entire column

    That was it, thanks!

+ 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] Set ranged from already set range - entire column without header
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-25-2018, 09:48 AM
  2. copy entire row from sheet1 into sheet2 matching ID and column header
    By dileepkmr319 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2018, 12:02 PM
  3. If column range contains specified column, concatenate matching values
    By johndon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-14-2016, 04:40 PM
  4. Need help using VBA to select entire column as Range
    By Roxie#2013 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-18-2014, 04:16 PM
  5. [SOLVED] Got range of a cell, now modify that to be range of entire column
    By ShawnW in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-19-2013, 11:46 AM
  6. [SOLVED] Marcro to copy range not entire column
    By slohman in forum Excel General
    Replies: 5
    Last Post: 06-19-2012, 03:32 AM
  7. how to call an entire column/range of cells
    By papermoon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2010, 01:31 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