FORMULA REQUIRED

[freds]

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

"freds" <freds@discussions.microsoft.com> wrote in message
news:3CAB38C3-081B-4CD6-8316-2A5AA35B0EC8@microsoft.com...
> 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.

[freds]

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" <freds@discussions.microsoft.com> wrote in message
> news:3CAB38C3-081B-4CD6-8316-2A5AA35B0EC8@microsoft.com...
> > 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

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" <freds@discussions.microsoft.com> wrote in message
news:E7AD7845-76C4-49AB-8033-486636F60B4B@microsoft.com...
> 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" <freds@discussions.microsoft.com> wrote in message
>> news:3CAB38C3-081B-4CD6-8316-2A5AA35B0EC8@microsoft.com...
>> > 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.
>>
>>
>>

[freds]

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" <freds@discussions.microsoft.com> wrote in message
> news:E7AD7845-76C4-49AB-8033-486636F60B4B@microsoft.com...
> > 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" <freds@discussions.microsoft.com> wrote in message
> >> news:3CAB38C3-081B-4CD6-8316-2A5AA35B0EC8@microsoft.com...
> >> > 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]

you're welcome - thanks for the feedback

"freds" <freds@discussions.microsoft.com> wrote in message
news:F9DAC272-A4D0-4289-AC6A-ADA95DCFE10F@microsoft.com...
> 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" <freds@discussions.microsoft.com> wrote in message
>> news:E7AD7845-76C4-49AB-8033-486636F60B4B@microsoft.com...
>> > 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" <freds@discussions.microsoft.com> wrote in message
>> >> news:3CAB38C3-081B-4CD6-8316-2A5AA35B0EC8@microsoft.com...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>