OFFSET FUNCTION: Syntax or a missing Formula:

[niceguy21]

=OFFSET( CONCATENATE( "C", MATCH("United Kingdom",$A:$A, 0)) , 0 , 0 , COUNTIF(A:A, "=United Kingdom"), 1)

The aim here is to "construct" a cell reference for the OFFSET Function. I.E Our column is always C, but our MATCH function outputs varying row numbers:

we want a formula that takes a fixed column, a MATCH output number and generates a cell reference from them. I think the problem here is the OFFSET function is not reading the reference here as its text.

Is there another approach to this?

Regards,

Koto

[Pepe Le Mokko]

You can use INDEX to return a reference to a cell or a range which can be used directly in another formula/function

[niceguy21]

You can use INDEX to return a reference to a cell or a range which can be used directly in another formula/function

=OFFSET( INDEX($A:$A,MATCH("United Kingdom",$A:$A, 0), 3), 0, 0, COUNTIF(A:A, "=United Kingdom"), 1)

tried this, but i'm getting REF! error

[TheExcelphile]

You could use the INDIRECT function. It takes a text string, and converts into a cell reference.
Your formula would become:

=OFFSET( INDIRECT(CONCATENATE( "C", MATCH("United Kingdom",$A:$A, 0))) , 0 , 0 , COUNTIF(A:A, "=United Kingdom"), 1)

[niceguy21]

=OFFSET( INDIRECT(ADDRESS(MATCH("United Kingdom",$A:$A, 0), 3)), 0, 0, COUNTIF(A:A, "=United Kingdom"), 1)


I'm happy with this, approach... all formulae are evaluating correctly within the OFFSET, but its saying error!

any suggestions?

Is it because address outputs text?

[TheExcelphile]

yes, you'll still need to use the INDIRECT formula.
Also, if you are using the OFFSET formula in a cell and the formula is returning a range instead of a single cell, Excel will give you an error.

[niceguy21]

yes, you'll still need to use the INDIRECT formula.
Also, if you are using the OFFSET formula in a cell and the formula is returning a range instead of a single cell, Excel will give you an error.

gotcha^^ I though it would neatly output a cell range for me

[niceguy21]

yes, you'll still need to use the INDIRECT formula.
Also, if you are using the OFFSET formula in a cell and the formula is returning a range instead of a single cell, Excel will give you an error.

gotcha^^ I though it would neatly output a cell range for me

[Pepe Le Mokko]

=OFFSET( INDEX($A:$A,MATCH("United Kingdom",$A:$A, 0), 3), 0, 0, COUNTIF(A:A, "=United Kingdom"), 1)

tried this, but i'm getting REF! error

Suggest you read this on what INDEX really does