Hi, I'm wondering whether anyone can help me with a formula in Excel which
I'm having trouble with.

eg:
Row 1. Col A value = 10, B = 12, C = 14, D = 16, E = 18, F = 20

Row 2. Col A value = 14

The values in Rows 1 and 2 are dynamic and may change based on other
formulas elsewhere in the Worksheet.

But Row 2 will always have a value that equals a value in Row 1.

Result Required:
I would like to find the CELL reference from Row 1 for the number that
matches that in Row 2. The Result in this case = $C$1. However the formula
must work everytime based on the fact that values can change in all fields.

I know how to find the Cell that matches 14 separately, and I know how to
get a Cell Address separately. However I can't manage to get a combined
formula that will always give me the correct cell reference for the numbers
that match.

Hi

try
=ADDRESS(1,MATCH(A2,A1:F1,0))
where 1 is the row number of your list of values (ie the range A1:F1) and A2
is the value to match to.


Cheers
JulieD

"freds" wrote in message
...
Hi, I'm wondering whether anyone can help me with a formula in Excel which
I'm having trouble with.

eg:
Row 1. Col A value = 10, B = 12, C = 14, D = 16, E = 18, F = 20

Row 2. Col A value = 14

The values in Rows 1 and 2 are dynamic and may change based on other
formulas elsewhere in the Worksheet.

But Row 2 will always have a value that equals a value in Row 1.

Result Required:
I would like to find the CELL reference from Row 1 for the number that
matches that in Row 2. The Result in this case = $C$1. However the formula
must work everytime based on the fact that values can change in all
fields.

I know how to find the Cell that matches 14 separately, and I know how to
get a Cell Address separately. However I can't manage to get a combined
formula that will always give me the correct cell reference for the
numbers
that match.

Hi JulieD,

Your formula was helpful, but doesn't quite work. Also it is a little more
complicated than I first wrote, as there are 2 additional columns.

eg:
Row 1. Col A = Text, Col B = Text, Col C = 10, D = 12, E = 14, F = 16, G =
18, H = 20

Row 2. Col A value = 14

The result needs to be E1 in this scenario, but I'm having trouble
understanding the ADDRESS syntax! Your formula ADDRESS(1,MATCH(A2,A1:F1,0))
returned a value A1 based on my previous example, however when I try to add
in a Column reference into the formula, I don't quite know what to put as the
Column may always be different, depending on what the various values are.

If you can shed any further light on this it would be greatly appreciated.

Cheers and have a great day.

"JulieD" wrote:

Hi

try
=ADDRESS(1,MATCH(A2,A1:F1,0))
where 1 is the row number of your list of values (ie the range A1:F1) and A2
is the value to match to.


Cheers
JulieD

"freds" wrote in message
...
Hi, I'm wondering whether anyone can help me with a formula in Excel which
I'm having trouble with.

eg:
Row 1. Col A value = 10, B = 12, C = 14, D = 16, E = 18, F = 20

Row 2. Col A value = 14

The values in Rows 1 and 2 are dynamic and may change based on other
formulas elsewhere in the Worksheet.

But Row 2 will always have a value that equals a value in Row 1.

Result Required:
I would like to find the CELL reference from Row 1 for the number that
matches that in Row 2. The Result in this case = $C$1. However the formula
must work everytime based on the fact that values can change in all
fields.

I know how to find the Cell that matches 14 separately, and I know how to
get a Cell Address separately. However I can't manage to get a combined
formula that will always give me the correct cell reference for the
numbers
that match.

Hi

the formula of
=ADDRESS(1,MATCH(A2,A1:F1,0))

says
return the cell address of the value in row 1 where a match for the value in
A2 is found in the range A1:F1.
so if you have additional columns all you need to change is the range of
values to check, ie A1:F1 becomes A1:H1

the syntax of the ADDRESS function is
ADDRESS(row_number, column_number)
to get the column number i'm using the MATCH function, the syntax is
MATCH(lookup_value, range_to_find_the_lookup_value_in,type_of_match)

hope this helps

Cheers
JulieD



"freds" wrote in message
...
Hi JulieD,

Your formula was helpful, but doesn't quite work. Also it is a little more
complicated than I first wrote, as there are 2 additional columns.

eg:
Row 1. Col A = Text, Col B = Text, Col C = 10, D = 12, E = 14, F = 16, G =
18, H = 20

Row 2. Col A value = 14

The result needs to be E1 in this scenario, but I'm having trouble
understanding the ADDRESS syntax! Your formula
ADDRESS(1,MATCH(A2,A1:F1,0))
returned a value A1 based on my previous example, however when I try to
add
in a Column reference into the formula, I don't quite know what to put as
the
Column may always be different, depending on what the various values are.

If you can shed any further light on this it would be greatly appreciated.

Cheers and have a great day.

"JulieD" wrote:

Hi

try
=ADDRESS(1,MATCH(A2,A1:F1,0))
where 1 is the row number of your list of values (ie the range A1:F1) and
A2
is the value to match to.


Cheers
JulieD

"freds" wrote in message
...
Hi, I'm wondering whether anyone can help me with a formula in Excel
which
I'm having trouble with.

eg:
Row 1. Col A value = 10, B = 12, C = 14, D = 16, E = 18, F = 20

Row 2. Col A value = 14

The values in Rows 1 and 2 are dynamic and may change based on other
formulas elsewhere in the Worksheet.

But Row 2 will always have a value that equals a value in Row 1.

Result Required:
I would like to find the CELL reference from Row 1 for the number that
matches that in Row 2. The Result in this case = $C$1. However the
formula
must work everytime based on the fact that values can change in all
fields.

I know how to find the Cell that matches 14 separately, and I know how
to
get a Cell Address separately. However I can't manage to get a combined
formula that will always give me the correct cell reference for the
numbers
that match.

Hi Julie,

Thanks, it worked.

"JulieD" wrote:

Hi

the formula of
=ADDRESS(1,MATCH(A2,A1:F1,0))

says
return the cell address of the value in row 1 where a match for the value in
A2 is found in the range A1:F1.
so if you have additional columns all you need to change is the range of
values to check, ie A1:F1 becomes A1:H1

the syntax of the ADDRESS function is
ADDRESS(row_number, column_number)
to get the column number i'm using the MATCH function, the syntax is
MATCH(lookup_value, range_to_find_the_lookup_value_in,type_of_match)

hope this helps

Cheers
JulieD



"freds" wrote in message
...
Hi JulieD,

Your formula was helpful, but doesn't quite work. Also it is a little more
complicated than I first wrote, as there are 2 additional columns.

eg:
Row 1. Col A = Text, Col B = Text, Col C = 10, D = 12, E = 14, F = 16, G =
18, H = 20

Row 2. Col A value = 14

The result needs to be E1 in this scenario, but I'm having trouble
understanding the ADDRESS syntax! Your formula
ADDRESS(1,MATCH(A2,A1:F1,0))
returned a value A1 based on my previous example, however when I try to
add
in a Column reference into the formula, I don't quite know what to put as
the
Column may always be different, depending on what the various values are.

If you can shed any further light on this it would be greatly appreciated.

Cheers and have a great day.

"JulieD" wrote:

Hi

try
=ADDRESS(1,MATCH(A2,A1:F1,0))
where 1 is the row number of your list of values (ie the range A1:F1) and
A2
is the value to match to.


Cheers
JulieD

"freds" wrote in message
...
Hi, I'm wondering whether anyone can help me with a formula in Excel
which
I'm having trouble with.

eg:
Row 1. Col A value = 10, B = 12, C = 14, D = 16, E = 18, F = 20

Row 2. Col A value = 14

The values in Rows 1 and 2 are dynamic and may change based on other
formulas elsewhere in the Worksheet.

But Row 2 will always have a value that equals a value in Row 1.

Result Required:
I would like to find the CELL reference from Row 1 for the number that
matches that in Row 2. The Result in this case = $C$1. However the
formula
must work everytime based on the fact that values can change in all
fields.

I know how to find the Cell that matches 14 separately, and I know how
to
get a Cell Address separately. However I can't manage to get a combined
formula that will always give me the correct cell reference for the
numbers
that match.

you're welcome - thanks for the feedback

"freds" wrote in message
...
Hi Julie,

Thanks, it worked.

"JulieD" wrote:

Hi

the formula of
=ADDRESS(1,MATCH(A2,A1:F1,0))

says
return the cell address of the value in row 1 where a match for the value
in
A2 is found in the range A1:F1.
so if you have additional columns all you need to change is the range of
values to check, ie A1:F1 becomes A1:H1

the syntax of the ADDRESS function is
ADDRESS(row_number, column_number)
to get the column number i'm using the MATCH function, the syntax is
MATCH(lookup_value, range_to_find_the_lookup_value_in,type_of_match)

hope this helps

Cheers
JulieD



"freds" wrote in message
...
Hi JulieD,

Your formula was helpful, but doesn't quite work. Also it is a little
more
complicated than I first wrote, as there are 2 additional columns.

eg:
Row 1. Col A = Text, Col B = Text, Col C = 10, D = 12, E = 14, F = 16,
G =
18, H = 20

Row 2. Col A value = 14

The result needs to be E1 in this scenario, but I'm having trouble
understanding the ADDRESS syntax! Your formula
ADDRESS(1,MATCH(A2,A1:F1,0))
returned a value A1 based on my previous example, however when I try to
add
in a Column reference into the formula, I don't quite know what to put
as
the
Column may always be different, depending on what the various values
are.

If you can shed any further light on this it would be greatly
appreciated.

Cheers and have a great day.

"JulieD" wrote:

Hi

try
=ADDRESS(1,MATCH(A2,A1:F1,0))
where 1 is the row number of your list of values (ie the range A1:F1)
and
A2
is the value to match to.


Cheers
JulieD

"freds" wrote in message
...
Hi, I'm wondering whether anyone can help me with a formula in Excel
which
I'm having trouble with.

eg:
Row 1. Col A value = 10, B = 12, C = 14, D = 16, E = 18, F = 20

Row 2. Col A value = 14

The values in Rows 1 and 2 are dynamic and may change based on other
formulas elsewhere in the Worksheet.

But Row 2 will always have a value that equals a value in Row 1.

Result Required:
I would like to find the CELL reference from Row 1 for the number
that
matches that in Row 2. The Result in this case = $C$1. However the
formula
must work everytime based on the fact that values can change in all
fields.

I know how to find the Cell that matches 14 separately, and I know
how
to
get a Cell Address separately. However I can't manage to get a
combined
formula that will always give me the correct cell reference for the
numbers
that match.