VLOOKUP Problem
 

I was grateful to Mike for his response to my query back in October and the
suggestion works fine. I have, however, been puzzled by the syntax ever
since.

Could somebody explain to me the "6.022*10^23" element? I'm presuming that
the '*' and '^' are wildcards of some sort but haven't been able to work it
out!

Many thanks.


"Mike H" wrote in message
...
Hi,

To get the last value in a row use this which is for row 2. The formula
can
be dragged down for subsequent rows.

=INDEX(2:2,MATCH(6.022*10^23,2:2))

Mike

"Terry Bennett" wrote:

I know that there will be a simple solution to this but have been playing
with it for 1/2 hour now and not making progress!

I have a table of data (say A1:Z100). An extra column is added each week
when various figures are received. Hence, at the moment, I have a list
of
branches in column A and dates across the top in Row 1.

All I need to do is to populate a summary with data extracted from the
most
recent figures. I can do this easily with VLOOKUP but how do I programme
it
such that Col_index_num returns data from the column with the most recent
date in Row 1? I have tried using MAX but can't seem to find the right
syntax.

Many thanks.

VLOOKUP Problem
 

6.022*10^23 is just a very large number, i.e. 6.022 times 10 raised to
the power of 23. It is fairly arbitrary - it needs to be larger than
any of the values that you expect in the cells. Here's how the formula
works:

Suppose you have these values in row 2:

2 5 7 11 16 23 31 33 47

then MATCH(100,2:2)

will find the highest cell value which is less than or equal to the
search value (in this case 100, as it is easier to write than
6.022*10^23). Hence it will match with 47, and return 9. As this is
within an INDEX function, the formula will return the value from the
9th cell in its range, i.e. 47.

So, the number just has to be larger than the numbers you expect - I
often use 9E99 or 10E100 as they are easy to type.

Hope this helps.

Pete

On Jan 29, 2:43*pm, "Terry Bennett" wrote:
I was grateful to Mike for his response to my query back in October and the
suggestion works fine. *I have, however, been puzzled by the syntax ever
since.

Could somebody explain to me the "6.022*10^23" element? *I'm presuming that
the '*' and '^' are wildcards of some sort but haven't been able to work it
out!

Many thanks.

"Mike H" wrote in message

...



Hi,

To get the last value in a row use this which is for row 2. The formula
can
be dragged down for subsequent rows.

=INDEX(2:2,MATCH(6.022*10^23,2:2))

Mike

"Terry Bennett" wrote:

I know that there will be a simple solution to this but have been playing
with it for 1/2 hour now and not making progress!

I have a table of data (say A1:Z100). *An extra column is added each week
when various figures are received. *Hence, at the moment, I have a list
of
branches in column A and dates across the top in Row 1.

All I need to do is to populate a summary with data extracted from the
most
recent figures. *I can do this easily with VLOOKUP but how do I programme
it
such that Col_index_num returns data from the column with the most recent
date in Row 1? *I have tried using MAX but can't seem to find the right
syntax.

Many thanks.- Hide quoted text -

- Show quoted text -

VLOOKUP Problem
 

This is the exponential or scientific method of displaying a very large number:

6.022 times 10 to the 23rd power
or
6.022E+23
or
602200000000000000000000


--
Gary''s Student - gsnu2007L


"Terry Bennett" wrote:

I was grateful to Mike for his response to my query back in October and the
suggestion works fine. I have, however, been puzzled by the syntax ever
since.

Could somebody explain to me the "6.022*10^23" element? I'm presuming that
the '*' and '^' are wildcards of some sort but haven't been able to work it
out!

Many thanks.


"Mike H" wrote in message
...
Hi,

To get the last value in a row use this which is for row 2. The formula
can
be dragged down for subsequent rows.

=INDEX(2:2,MATCH(6.022*10^23,2:2))

Mike

"Terry Bennett" wrote:

I know that there will be a simple solution to this but have been playing
with it for 1/2 hour now and not making progress!

I have a table of data (say A1:Z100). An extra column is added each week
when various figures are received. Hence, at the moment, I have a list
of
branches in column A and dates across the top in Row 1.

All I need to do is to populate a summary with data extracted from the
most
recent figures. I can do this easily with VLOOKUP but how do I programme
it
such that Col_index_num returns data from the column with the most recent
date in Row 1? I have tried using MAX but can't seem to find the right
syntax.

Many thanks.

VLOOKUP Problem
 

Thanks Pete - I now understand.

Your explanation has, however, made me wonder whether the formula I have
will achieve the result I need. You will see from my original post that I
need a method of returning the value from the most recent column (or that to
the furthest right on the worksheet). The numbers will not always increase
week by week - would the formula I have simply return the value of the
largest amount in the row?

"Pete_UK" wrote in message
...
6.022*10^23 is just a very large number, i.e. 6.022 times 10 raised to
the power of 23. It is fairly arbitrary - it needs to be larger than
any of the values that you expect in the cells. Here's how the formula
works:

Suppose you have these values in row 2:

2 5 7 11 16 23 31 33 47

then MATCH(100,2:2)

will find the highest cell value which is less than or equal to the
search value (in this case 100, as it is easier to write than
6.022*10^23). Hence it will match with 47, and return 9. As this is
within an INDEX function, the formula will return the value from the
9th cell in its range, i.e. 47.

So, the number just has to be larger than the numbers you expect - I
often use 9E99 or 10E100 as they are easy to type.

Hope this helps.

Pete

On Jan 29, 2:43 pm, "Terry Bennett" wrote:
I was grateful to Mike for his response to my query back in October and
the
suggestion works fine. I have, however, been puzzled by the syntax ever
since.

Could somebody explain to me the "6.022*10^23" element? I'm presuming that
the '*' and '^' are wildcards of some sort but haven't been able to work
it
out!

Many thanks.

"Mike H" wrote in message

...



Hi,

To get the last value in a row use this which is for row 2. The formula
can
be dragged down for subsequent rows.

=INDEX(2:2,MATCH(6.022*10^23,2:2))

Mike

"Terry Bennett" wrote:

I know that there will be a simple solution to this but have been
playing
with it for 1/2 hour now and not making progress!

I have a table of data (say A1:Z100). An extra column is added each
week
when various figures are received. Hence, at the moment, I have a list
of
branches in column A and dates across the top in Row 1.

All I need to do is to populate a summary with data extracted from the
most
recent figures. I can do this easily with VLOOKUP but how do I
programme
it
such that Col_index_num returns data from the column with the most
recent
date in Row 1? I have tried using MAX but can't seem to find the right
syntax.

Many thanks.- Hide quoted text -

- Show quoted text -