First time here? Check out the FAQ!
![]() | 1 | initial version |
Hi,
thank you for your effort. Fortunately after many attempts, I solved my problem.
I explain how I get the solution: These two formulas allow me to find the range coordinate via ADDRESS function:
AS12=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1)
let say $A$10
AT12=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1)
let say $C$10
If I use the following formula, I get 0 (zero) as @Lupp suggested:
INTERCEPT(INDIRECT(AS12 & ":$C$100"),INDIRECT(AT12 & ":$A$100"))
Finally, the following two formulas produce equivalent results:
INTERCEPT(INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1)):$C$100,INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1)):$A$100)
INTERCEPT(INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1) & ":$C$132"),INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1) & ":$A$100"))
However, the former is better choice because it allows to dynamically change the range extreme without modifying the formula. Best regards.
![]() | 2 | No.2 Revision |
Hi,
thank you for your effort. Fortunately after many attempts, I solved my problem.
I explain how I get the solution:
solution.
These two formulas allow me to find the range coordinate via ADDRESS function:
AS12=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1)
let say $A$10
AT12=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1)
let say $C$10
If I use the following formula, I get 0 (zero) as @Lupp suggested:
INTERCEPT(INDIRECT(AS12 & ":$C$100"),INDIRECT(AT12 & ":$A$100"))
Finally, the following two formulas produce equivalent results:
INTERCEPT(INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1)):$C$100,INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1)):$A$100)
INTERCEPT(INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1) & ":$C$132"),INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1) & ":$A$100"))
However, the former is better choice because it allows to dynamically change the range extreme without modifying the formula. Best regards.
![]() | 3 | No.3 Revision |
Hi,
thank you for your effort. Fortunately after many attempts, I solved my problem.
I explain how I get the solution.
These two formulas allow me to find the range coordinate via ADDRESS function:
AS12=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1)
let say $A$10
AT12=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1)
let say $C$10
If I use the following formula, I get 0 (zero) as @Lupp suggested:
INTERCEPT(INDIRECT(AS12 & ":$C$100"),INDIRECT(AT12 & ":$A$100"))
Finally, the following two formulas produce equivalent results:
INTERCEPT(INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1)):$C$100,INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1)):$A$100)
INTERCEPT(INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1) & ":$C$132"),INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1) & ":$A$100"))
However, the former is a better choice because it allows to dynamically change the range extreme extremes without modifying the formula.
formula.
Best regards.