Forum Discussion
Index and match
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
Better 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)
7 Replies
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.
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
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.
