Revision history [back]

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.

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.

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.