Forum Discussion
Returning the range for a specific value
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
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.
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.
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.
