Sep 11 2020 05:09 PM - edited Sep 11 2020 05:10 PM
Hi,
i am trying to write an index formula for the following two tables. I want to write an array formula that returns
13 | 26 | 39 | 52 | 65 |
by matching 311.585 number from the following two table sets
5 | 0 | 1 | 311.535 | 0 | |
8 | 10 | 20 | 30 | 40 | 50 |
53 | 12 | 24 | 36 | 48 | 60 |
15 | 0 | 1 | 311.585 | 0 | |
12 | 13 | 26 | 39 | 52 | 65 |
1008 | 19 | 38 | 57 | 76 | 95 |
Basically my problem is that i can't tell the formula to look up 311.585 and return the table number as 2 when typing the area_num for the array function. Also when i type the formula it starts returning values as
12 | 13 | 26 | 39 | 52 | 65 |
instead of
13 | 26 | 39 | 52 | 65 |
Please help
Thanks
Sep 12 2020 03:11 AM
Could you please clarify why returned numbers shall start from12, not 13 and how it is connected to the position of 311.535 or 311.585 (they are different in your sample). As variant that could be
but that's another logic.
Sep 12 2020 10:32 AM
hi i want a formula that looks up these two tables based on my lookup value of 311.535 or 311.585 and returns an array that starts from second column and second row
Sep 12 2020 01:27 PM
and lookup value could be in any position or always in the 4th column from the range start? Other words, how do we know where is the first column of the range.
Second row as I understood is the next to the one in which the value found.
Another question is what is the logic of how to find end of the range - is it of predefined size or it shall be found based on some logic.
Sep 12 2020 01:48 PM - edited Sep 12 2020 01:55 PM
The lookup value will always be in 4th column
second answer is that it there is a predefined size to the array
Please see attached
Sep 12 2020 02:12 PM
SolutionBetter to have sample file instead of screenshot, but for such model
formula could be
=OFFSET(INDEX(F:F,MATCH($K5,F:F,0)),COUNTIF($K$5:$K5,$K5),-2,1,6)
Sep 12 2020 03:59 PM
This works exactly how i wanted. Thank you so much.
Sep 12 2020 02:12 PM
SolutionBetter to have sample file instead of screenshot, but for such model
formula could be
=OFFSET(INDEX(F:F,MATCH($K5,F:F,0)),COUNTIF($K$5:$K5,$K5),-2,1,6)