Results 1 to 4 of 4

Thread: Match, Index, VLookup, Hlookup

  1. #1

    Match, Index, VLookup, Hlookup



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,

    I previously requested help on a different forum but but am still no wiser.

    I am trying to identify the value of label at the top column for a value selected within the core of a table.

    Using Match(1300,B5:I5,-1) returns value 7, which is the value I am interested in. Note that Column 1 is 'B'.

    Having found 7, i.e. Column number 7 starting from B, I need to know the value at the top of Column 7. And this is where I am stuck.

    A B C D E F G H I
    1 Type 1 Type 2 Type 3 Type 4 Type 5 Type 6 Type 7 Type 8
    2 Level 1 UL 2,232 1,488 2,232 1,488 1,907 1,116 651
    3 Level 2 UL UL UL UL UL UL UL UL
    4 Level 3 4,464 2,418 1,628 2,418 1,628 2,372 1,302 837
    5 Level 4 7,347 3,627 2,418 3,627 2,418 3,581 1,953 1,256


    Anyone has any idea? i think i basically need a function that allows me to return the value "Type 7" after using the afforested MATCH function.

    Many thanks.
    Last edited by lricher; 2015-02-02 at 08:49 PM.

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    808
    Articles
    0
    Excel Version
    Excel 2010
    If your formula was MATCH(3500,B5:I5,-1) what column would you expect your answer to be from ?

    I don't think that formula is going to work for you as it would require the search range to be sorted in descending order, which it isn't.
    see this.

    But none the less, Match will return the column number within the specified range, so the formula you want could be like this
    =INDEX(B1:I1,1,MATCH(1300,B5:I5,-1))

  3. #3
    Thanks i will try this.

    In answer to your question, the answer to the formula MATCH(3500,B5:I5,-1) is "Type 4", as i would need the closest match from the right to the left (if this makes sense)

    Lou

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    808
    Articles
    0
    Excel Version
    Excel 2010
    No, it doesn't make sense because the 3,581 under Type 6 is the smallest value larger than 3500.

    When you actually try the formula on your posted data it returns 2.
    This is why the search data being sorted in descending value is a requirement of using the Match function.

    I don't think Match is the function you're in need of, but must confess don't know what function is.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •