Index, Match

[DaveM]

Hi,

I have a 2 columns of data that is downloaded from an extrernal source. The
list varies in length. At the bottom of the entries in column B is a cell
that calculates the average of all the entries above. The cell to the left is
titled 'Total Average (xxx)' where xxx is the number of entries.

I need to link to the Total Average figure from another worksheet.

I have been using the Index, Match function elsewhere. But because xxx
varies this is not possible.

Can I somehow use the Left function with Index Match such that only Total
Average is the Matched variable? And if so how do I write the formula for
this?

Or can somebody suggest an alternative method?

Thanks

Dave

[Andrew Taylor]

Assuming column B contains only the downloaded numbers,
and that there are no non-numeric entries in the list, then
=COUNT(B:B) tell you how many numbers there are (including
the average). So you can get the average from another sheet
with the formula:

=OFFSET(Sheet1!B1,COUNT(Sheet1!B:B)-1,0)

(change Sheet1 to the name of your worksheet)




DaveM wrote:
> Hi,
>
> I have a 2 columns of data that is downloaded from an extrernal source. The
> list varies in length. At the bottom of the entries in column B is a cell
> that calculates the average of all the entries above. The cell to the left is
> titled 'Total Average (xxx)' where xxx is the number of entries.
>
> I need to link to the Total Average figure from another worksheet.
>
> I have been using the Index, Match function elsewhere. But because xxx
> varies this is not possible.
>
> Can I somehow use the Left function with Index Match such that only Total
> Average is the Matched variable? And if so how do I write the formula for
> this?
>
> Or can somebody suggest an alternative method?
>
> Thanks
>
> Dave

[Toppers]

Dave,
Try ...

=INDEX(E1:E100,MATCH("*Total Average*",B1:B100,),1)

HTH

"DaveM" wrote:

> Hi,
>
> I have a 2 columns of data that is downloaded from an extrernal source. The
> list varies in length. At the bottom of the entries in column B is a cell
> that calculates the average of all the entries above. The cell to the left is
> titled 'Total Average (xxx)' where xxx is the number of entries.
>
> I need to link to the Total Average figure from another worksheet.
>
> I have been using the Index, Match function elsewhere. But because xxx
> varies this is not possible.
>
> Can I somehow use the Left function with Index Match such that only Total
> Average is the Matched variable? And if so how do I write the formula for
> this?
>
> Or can somebody suggest an alternative method?
>
> Thanks
>
> Dave

[Ron Coderre]

Try this:

With:
Your list in columns A:B on Sheet2
NO other items in A:B except for the list

Then....
In a cell on Sheet1:
=LOOKUP(10^99,Sheet2!B:B)
That formula returns the last numeric item from Col_B on Sheet2

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"DaveM" wrote:

> Hi,
>
> I have a 2 columns of data that is downloaded from an extrernal source. The
> list varies in length. At the bottom of the entries in column B is a cell
> that calculates the average of all the entries above. The cell to the left is
> titled 'Total Average (xxx)' where xxx is the number of entries.
>
> I need to link to the Total Average figure from another worksheet.
>
> I have been using the Index, Match function elsewhere. But because xxx
> varies this is not possible.
>
> Can I somehow use the Left function with Index Match such that only Total
> Average is the Matched variable? And if so how do I write the formula for
> this?
>
> Or can somebody suggest an alternative method?
>
> Thanks
>
> Dave

[DaveM]

Hi Toppers
Thanks for your response.

I changed E1:E100 to the named range I inserted for the whole array and
changed B1:B100 to the named range containing my look up value 'Total
Average', then changed the look up value from 1 to 2

and

that's done the trick nicely. Many thanks

Does the * just signify that anything could be in front or after my Match
value of Total Average?


"Toppers" wrote:

> Dave,
> Try ...
>
> =INDEX(E1:E100,MATCH("*Total Average*",B1:B100,),1)
>
> HTH
>
> "DaveM" wrote:
>
> > Hi,
> >
> > I have a 2 columns of data that is downloaded from an extrernal source. The
> > list varies in length. At the bottom of the entries in column B is a cell
> > that calculates the average of all the entries above. The cell to the left is
> > titled 'Total Average (xxx)' where xxx is the number of entries.
> >
> > I need to link to the Total Average figure from another worksheet.
> >
> > I have been using the Index, Match function elsewhere. But because xxx
> > varies this is not possible.
> >
> > Can I somehow use the Left function with Index Match such that only Total
> > Average is the Matched variable? And if so how do I write the formula for
> > this?
> >
> > Or can somebody suggest an alternative method?
> >
> > Thanks
> >
> > Dave

[DaveM]

Hi Andrew

Thanks for this. It did not quite work how I wanted because the data is a
filtered list and the average comes from the unfiltered list. But there are
two other suggetsions here that do the trick.

Thanks
Dave

"Andrew Taylor" wrote:

> Assuming column B contains only the downloaded numbers,
> and that there are no non-numeric entries in the list, then
> =COUNT(B:B) tell you how many numbers there are (including
> the average). So you can get the average from another sheet
> with the formula:
>
> =OFFSET(Sheet1!B1,COUNT(Sheet1!B:B)-1,0)
>
> (change Sheet1 to the name of your worksheet)
>
>
>
>
> DaveM wrote:
> > Hi,
> >
> > I have a 2 columns of data that is downloaded from an extrernal source. The
> > list varies in length. At the bottom of the entries in column B is a cell
> > that calculates the average of all the entries above. The cell to the left is
> > titled 'Total Average (xxx)' where xxx is the number of entries.
> >
> > I need to link to the Total Average figure from another worksheet.
> >
> > I have been using the Index, Match function elsewhere. But because xxx
> > varies this is not possible.
> >
> > Can I somehow use the Left function with Index Match such that only Total
> > Average is the Matched variable? And if so how do I write the formula for
> > this?
> >
> > Or can somebody suggest an alternative method?
> >
> > Thanks
> >
> > Dave
>
>

[DaveM]

Hi Ron

This also works thank you.

But I am not too sure how the formula works - I don't understand what it is
telling Excel to do.

What does '10^99' mean? I presume this means go to the last value in that
column
but I don't understand why it works and before I use the formula it would be
nice to know if there are any limitations to its use.

Thanks
Dave
"Ron Coderre" wrote:

> Try this:
>
> With:
> Your list in columns A:B on Sheet2
> NO other items in A:B except for the list
>
> Then....
> In a cell on Sheet1:
> =LOOKUP(10^99,Sheet2!B:B)
> That formula returns the last numeric item from Col_B on Sheet2
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "DaveM" wrote:
>
> > Hi,
> >
> > I have a 2 columns of data that is downloaded from an extrernal source. The
> > list varies in length. At the bottom of the entries in column B is a cell
> > that calculates the average of all the entries above. The cell to the left is
> > titled 'Total Average (xxx)' where xxx is the number of entries.
> >
> > I need to link to the Total Average figure from another worksheet.
> >
> > I have been using the Index, Match function elsewhere. But because xxx
> > varies this is not possible.
> >
> > Can I somehow use the Left function with Index Match such that only Total
> > Average is the Matched variable? And if so how do I write the formula for
> > this?
> >
> > Or can somebody suggest an alternative method?
> >
> > Thanks
> >
> > Dave

[Toppers]

Yes. The "*" is a 'wildcard ' but isn't required if your text starts with
"Total Average" but I inserted it to give a more generic solution.

And thanks for the feedback.

"DaveM" wrote:

> Hi Toppers
> Thanks for your response.
>
> I changed E1:E100 to the named range I inserted for the whole array and
> changed B1:B100 to the named range containing my look up value 'Total
> Average', then changed the look up value from 1 to 2
>
> and
>
> that's done the trick nicely. Many thanks
>
> Does the * just signify that anything could be in front or after my Match
> value of Total Average?
>
>
> "Toppers" wrote:
>
> > Dave,
> > Try ...
> >
> > =INDEX(E1:E100,MATCH("*Total Average*",B1:B100,),1)
> >
> > HTH
> >
> > "DaveM" wrote:
> >
> > > Hi,
> > >
> > > I have a 2 columns of data that is downloaded from an extrernal source. The
> > > list varies in length. At the bottom of the entries in column B is a cell
> > > that calculates the average of all the entries above. The cell to the left is
> > > titled 'Total Average (xxx)' where xxx is the number of entries.
> > >
> > > I need to link to the Total Average figure from another worksheet.
> > >
> > > I have been using the Index, Match function elsewhere. But because xxx
> > > varies this is not possible.
> > >
> > > Can I somehow use the Left function with Index Match such that only Total
> > > Average is the Matched variable? And if so how do I write the formula for
> > > this?
> > >
> > > Or can somebody suggest an alternative method?
> > >
> > > Thanks
> > >
> > > Dave

[DaveM]

Thanks for the * explanation

My pleasure
Dave
"Toppers" wrote:

> Yes. The "*" is a 'wildcard ' but isn't required if your text starts with
> "Total Average" but I inserted it to give a more generic solution.
>
> And thanks for the feedback.
>
> "DaveM" wrote:
>
> > Hi Toppers
> > Thanks for your response.
> >
> > I changed E1:E100 to the named range I inserted for the whole array and
> > changed B1:B100 to the named range containing my look up value 'Total
> > Average', then changed the look up value from 1 to 2
> >
> > and
> >
> > that's done the trick nicely. Many thanks
> >
> > Does the * just signify that anything could be in front or after my Match
> > value of Total Average?
> >
> >
> > "Toppers" wrote:
> >
> > > Dave,
> > > Try ...
> > >
> > > =INDEX(E1:E100,MATCH("*Total Average*",B1:B100,),1)
> > >
> > > HTH
> > >
> > > "DaveM" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a 2 columns of data that is downloaded from an extrernal source. The
> > > > list varies in length. At the bottom of the entries in column B is a cell
> > > > that calculates the average of all the entries above. The cell to the left is
> > > > titled 'Total Average (xxx)' where xxx is the number of entries.
> > > >
> > > > I need to link to the Total Average figure from another worksheet.
> > > >
> > > > I have been using the Index, Match function elsewhere. But because xxx
> > > > varies this is not possible.
> > > >
> > > > Can I somehow use the Left function with Index Match such that only Total
> > > > Average is the Matched variable? And if so how do I write the formula for
> > > > this?
> > > >
> > > > Or can somebody suggest an alternative method?
> > > >
> > > > Thanks
> > > >
> > > > Dave

[Ron Coderre]

Regarding =LOOKUP(10^99,Sheet2!B:B)

LOOKUP has a nice little quirk....
When the search_value is larger than any other value in the lookup range, it
simply returns the last item in the list that is the same type as the lookup
value (text vs numeric). In our case, that number is the Total Average that
you are looking for.

To ensure that there could not possibly be a match, many people use the
largest numeric value that Excel can handle (9.9999999999E-307). IMHO that's
only necessary in some scientific applications (astronomy for example), but
for my purposes, 10 to the 99th power is guarranteed to be larger than any
number I'll be looking for, plus it requires less typing and is cleaner
looking.

To find the last text value in Col_A, you'd use this variation:
=LOOKUP(REPT("z",255),A:A)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"DaveM" wrote:

> Hi Ron
>
> This also works thank you.
>
> But I am not too sure how the formula works - I don't understand what it is
> telling Excel to do.
>
> What does '10^99' mean? I presume this means go to the last value in that
> column
> but I don't understand why it works and before I use the formula it would be
> nice to know if there are any limitations to its use.
>
> Thanks
> Dave
> "Ron Coderre" wrote:
>
> > Try this:
> >
> > With:
> > Your list in columns A:B on Sheet2
> > NO other items in A:B except for the list
> >
> > Then....
> > In a cell on Sheet1:
> > =LOOKUP(10^99,Sheet2!B:B)
> > That formula returns the last numeric item from Col_B on Sheet2
> >
> > Is that something you can work with?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "DaveM" wrote:
> >
> > > Hi,
> > >
> > > I have a 2 columns of data that is downloaded from an extrernal source. The
> > > list varies in length. At the bottom of the entries in column B is a cell
> > > that calculates the average of all the entries above. The cell to the left is
> > > titled 'Total Average (xxx)' where xxx is the number of entries.
> > >
> > > I need to link to the Total Average figure from another worksheet.
> > >
> > > I have been using the Index, Match function elsewhere. But because xxx
> > > varies this is not possible.
> > >
> > > Can I somehow use the Left function with Index Match such that only Total
> > > Average is the Matched variable? And if so how do I write the formula for
> > > this?
> > >
> > > Or can somebody suggest an alternative method?
> > >
> > > Thanks
> > >
> > > Dave

[DaveM]

Thanks Ron
You just switched the light on!

"Ron Coderre" wrote:

> Regarding =LOOKUP(10^99,Sheet2!B:B)
>
> LOOKUP has a nice little quirk....
> When the search_value is larger than any other value in the lookup range, it
> simply returns the last item in the list that is the same type as the lookup
> value (text vs numeric). In our case, that number is the Total Average that
> you are looking for.
>
> To ensure that there could not possibly be a match, many people use the
> largest numeric value that Excel can handle (9.9999999999E-307). IMHO that's
> only necessary in some scientific applications (astronomy for example), but
> for my purposes, 10 to the 99th power is guarranteed to be larger than any
> number I'll be looking for, plus it requires less typing and is cleaner
> looking.
>
> To find the last text value in Col_A, you'd use this variation:
> =LOOKUP(REPT("z",255),A:A)
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "DaveM" wrote:
>
> > Hi Ron
> >
> > This also works thank you.
> >
> > But I am not too sure how the formula works - I don't understand what it is
> > telling Excel to do.
> >
> > What does '10^99' mean? I presume this means go to the last value in that
> > column
> > but I don't understand why it works and before I use the formula it would be
> > nice to know if there are any limitations to its use.
> >
> > Thanks
> > Dave
> > "Ron Coderre" wrote:
> >
> > > Try this:
> > >
> > > With:
> > > Your list in columns A:B on Sheet2
> > > NO other items in A:B except for the list
> > >
> > > Then....
> > > In a cell on Sheet1:
> > > =LOOKUP(10^99,Sheet2!B:B)
> > > That formula returns the last numeric item from Col_B on Sheet2
> > >
> > > Is that something you can work with?
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP
> > >
> > >
> > > "DaveM" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a 2 columns of data that is downloaded from an extrernal source. The
> > > > list varies in length. At the bottom of the entries in column B is a cell
> > > > that calculates the average of all the entries above. The cell to the left is
> > > > titled 'Total Average (xxx)' where xxx is the number of entries.
> > > >
> > > > I need to link to the Total Average figure from another worksheet.
> > > >
> > > > I have been using the Index, Match function elsewhere. But because xxx
> > > > varies this is not possible.
> > > >
> > > > Can I somehow use the Left function with Index Match such that only Total
> > > > Average is the Matched variable? And if so how do I write the formula for
> > > > this?
> > > >
> > > > Or can somebody suggest an alternative method?
> > > >
> > > > Thanks
> > > >
> > > > Dave