FORMULA REQUIRED

[Guest]

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.

 

[JulieD]

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

 

[Guest]

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]

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

 

[Guest]

Hi Julie,

Thanks, it worked.

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

 

[JulieD]

you're welcome - thanks for the feedback

Hi Julie,

Thanks, it worked.