VLOOKUP Problem

[Terry Bennett]

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.

 

[Don Guillett]

As ALWAYS, you should post your code for comments. If you want to find the
last column in row 1

lastcol=cells(1,columns.count).end(xltoleft).column

 

[Mike H]

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]

Thanks Mike. That seems to work fine but I'm intrigued with the syntax.
Can you enlighten me with the "6.022*10^23" part?!

 

[Terry Bennett]

Don. I appreciate you taking the trouble to reply but you're assuming a
greater level of knowledge than I possess!

I was looking for a function/functions and don't understand your suggestion.

 

[Don Guillett]

Mine was, IF you were using a macro. You didn't say.

 

[Terry Bennett]

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.

 

[David Biddulph]

If you are unfamiliar with Excel's arithmetic operators, I would suggest
that you type the term "arithmetic operators" into Excel help.

The * symbol in this context is a multiplication, not a wildcard. The
formula =2*3 gives the answer 6, which is the result of mutliplying 2 by 3.
The symbol ^ is the exponentiation or power operator. 10^23 means 10 to the
power of 23, or 1 with 23 zeroes after it (or 1E+23).
=10^23 is the same as =POWER(10,23)

In the suggested formula, Mike was doubtless trying to suggest a very large
number, and his choice wasn't entirely arbitrary. Google will explain its
significance.

 

[Dave Peterson]

That's just a giant number.

^ is the power operation

For instance:
2^4
is the same as
2*2*2*2
(16)

And the asterisk is just the multiply operation.

Type:
=6.022*10^23
and you'll see how big a number it represents.

 

[Terry Bennett]

Ah, yes, the Avogadro constant.

Thanks David.

If you are unfamiliar with Excel's arithmetic operators, I would suggest
that you type the term "arithmetic operators" into Excel help.

The * symbol in this context is a multiplication, not a wildcard. The
formula =2*3 gives the answer 6, which is the result of mutliplying 2 by
3.
The symbol ^ is the exponentiation or power operator. 10^23 means 10 to
the power of 23, or 1 with 23 zeroes after it (or 1E+23).
=10^23 is the same as =POWER(10,23)

In the suggested formula, Mike was doubtless trying to suggest a very
large number, and his choice wasn't entirely arbitrary. Google will
explain its significance.