Returning the range for a specific value

Copper Contributor

I'm not sure if this question has come up, but I'm trying to figure out how to look up a value in a source table and then return the nearest range value that is less than and greater than the value into 2 different columns in the original table.  My catch is that I also need to lookup a separate value in the original table to determine WHICH range to use.

 

I've attached an example sheet.  Basically I want to look up the value of Table1's MED_TRUE in Table2, look at Table1's DOSE_CALC value and return the first value that is less than DOSE_CALC into the NRST_VLS_DOWN column and the first value that is greater than DOSE_CALC into the NRST_VLS_UP column.

 

Hope that made sense.  Thanks for your help!

3 Replies

@umasirhcAssuming I understand your need, I would:

 

1) Add two helper columns to Table1: Row, Column

 

2) Row's purpose is to find the row in Table2 using formula:

=MATCH([@[MED_TRUE]],Table2[MED_TRUE],0) 

 

3) Column's purpose is to find the first match lower than DOSE_TRUE using formula:

=MATCH([@[DOSE_CALC]],OFFSET(Table2,[@Row]-1,0,1,COLUMNS(Table2)-1),1)

 

4) Now add NRST_VLS_DOWN using formula:

=INDEX(Table2,[@Row],[@Column])

 

5) Finally add NRST_VLS_UP using formula:

=INDEX(Table2,[@Row],[@Column]+1)

 

See attached. This is the solution that came off the top of my head. It works but I feel like there is a simple solution out there. Hopefully someone else will provide it. 

@umasirhc As a variant, similar but without helper columns and using XLOOKUP. So, only for MS365 subscribers! And I must say, it's not very elegant as the formula becomes too long to my liking.

@Craig HatmakerThanks!  You understood what I was going for.  Your solution works!  But, like you said, if a simpler solution exists, I'd be interested in that as well.