Results 1 to 6 of 6

Thread: Identify coloum after using VLOOKUP to find row

  1. #1
    Neophyte cully's Avatar
    Join Date
    Oct 2018
    Posts
    4
    Articles
    0
    Excel Version
    2017

    Identify coloum after using VLOOKUP to find row



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

    Hi Guys,

    In the example I have attached. I have the value 2, I need to look along that row to find the first value that it finds that sits between a range (in this case between 60 & 70, target 62). I then need it to return the green 00 number at the top of that column

    I'm using VLOOKUP(D18,A6:L15,2,FALSE) but instead of the 2 I need it to find the first number to the right that sits within a range (between 60&70) and then somehow return the green 00 number in the row above the target

    My ultimate aim is to return 006 (G2)

    I'm a new member so thanks in advance to anyone that can help.

    Cully.
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =IFERROR(INDEX($B$2:$L$2,MATCH(1,INDEX((INDEX($B$6:$L$15,D18,0)>=60)*(INDEX($B$6:$L$15,D18,0)<=70),0),0)),"Not Found")


  3. #3
    Neophyte cully's Avatar
    Join Date
    Oct 2018
    Posts
    4
    Articles
    0
    Excel Version
    2017
    Nailed It, Thank You very much for your skills NBVC

    cheers,

  4. #4
    Neophyte cully's Avatar
    Join Date
    Oct 2018
    Posts
    4
    Articles
    0
    Excel Version
    2017
    Hi, have actually applied it to my worksheet and lost the functionality. Not sure what I have done but If you notice my mistake I would like to Know. I think its an Indexing integer fault but unsure.

    In the attached "sheet 2" I have the number to look up in COLUMN D (B3= 1.40)
    I want it to find the first number along that row (16) for the first number between B6 & B7 Target should be O16= 105)
    I then need it to return the red number in ROW 2 (should be 11) but it is returning 17 in C9 (where my formula is)

    Hopefully you can clear that up
    Attached Files Attached Files

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    In your original sample, the first column in your data matched row numbers within the whole range.... so we could use the "2" in D18 directly as the row number parameter in the INDEX formula.... but now your first column are decimal figures, so you can't directly match up the value in B3 as the range row number... we will need to return the matching row number with the MATCH() funcition.

    Try updating formula to adapt...

    =IFERROR(INDEX($E$2:$AY$2,MATCH(1,INDEX((INDEX($E$7:$AY$58,MATCH(B3,$D$7:$D$58,0),0)>=B6)*(INDEX($E$7:$AY$58,MATCH(B3,$D$7:$D$58,0),0)<=B7),0),0)),"Not Found")
    Last edited by NBVC; 2018-10-11 at 01:22 PM.


  6. #6
    Neophyte cully's Avatar
    Join Date
    Oct 2018
    Posts
    4
    Articles
    0
    Excel Version
    2017
    Thanks NBVC,

    I actually numbered each of the rows in my first column 1,2,3,.... as an "address" (in a hidden data sheet) and used a VLOOKUP that row "address" into the D18 cell of your first suggestion which works fine.

    your second solution works perfectly too.

    Thank you

Posting Permissions

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