Identify coloum after using VLOOKUP to find row

cully

New member
Joined
Oct 10, 2018
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2017
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.
 

Attachments

  • VLOOKUP.xlsx
    10.4 KB · Views: 12
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")
 
Nailed It, Thank You very much for your skills NBVC

cheers,
 
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
 

Attachments

  • VLOOKUP2.xlsx
    49.2 KB · Views: 8
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:
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
 
Back
Top