VLOOKUP and LARGE
 

I have 8 columns and i need to look in column F1 thru F100 to find the 2nd
most recent date, and then pickup the corresponding number in column G from
the same row. I came up with the formula below but it doesn't seem to work.
Should it be an "IF" statement instead? OR am I missing something in my
formula

=VLOOKUP(MAXA('45'!$F$4:$F$100,2),'45'!$G$4:$G$100 ,7,'45'!$G$4:$G$100)

VLOOKUP and LARGE
 

You need LARGE not MAXA
=MATCH(LARGE('45'!$F$4:$F$100,2),0) will tell you the relative row of the
second largest value in the range
=INDEX('45'!$G$4:$G$100,MATCH(LARGE('45'!$F$4:$F$1 00,2),0) )
will find the corresponding item in the G column
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Public Utility 555" wrote in
message ...
I have 8 columns and i need to look in column F1 thru F100 to find the 2nd
most recent date, and then pickup the corresponding number in column G
from
the same row. I came up with the formula below but it doesn't seem to
work.
Should it be an "IF" statement instead? OR am I missing something in my
formula

=VLOOKUP(MAXA('45'!$F$4:$F$100,2),'45'!$G$4:$G$100 ,7,'45'!$G$4:$G$100)

VLOOKUP and LARGE
 

I'm getting a "n/A" error, does it make a difference that I'm working with
dates 2/22/2006 (format) in column "F"?

"Bernard Liengme" wrote:

You need LARGE not MAXA
=MATCH(LARGE('45'!$F$4:$F$100,2),0) will tell you the relative row of the
second largest value in the range
=INDEX('45'!$G$4:$G$100,MATCH(LARGE('45'!$F$4:$F$1 00,2),0) )
will find the corresponding item in the G column
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Public Utility 555" wrote in
message ...
I have 8 columns and i need to look in column F1 thru F100 to find the 2nd
most recent date, and then pickup the corresponding number in column G
from
the same row. I came up with the formula below but it doesn't seem to
work.
Should it be an "IF" statement instead? OR am I missing something in my
formula

=VLOOKUP(MAXA('45'!$F$4:$F$100,2),'45'!$G$4:$G$100 ,7,'45'!$G$4:$G$100)

VLOOKUP and LARGE
 

Dates are stored as numbers like 39234, the format affects only the display.
Send me the file (or a sample of the data) to my private email (remove
TRUENORTH)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"Public Utility 555" wrote in
message ...
I'm getting a "n/A" error, does it make a difference that I'm working with
dates 2/22/2006 (format) in column "F"?

"Bernard Liengme" wrote:

You need LARGE not MAXA
=MATCH(LARGE('45'!$F$4:$F$100,2),0) will tell you the relative row of the
second largest value in the range
=INDEX('45'!$G$4:$G$100,MATCH(LARGE('45'!$F$4:$F$1 00,2),0) )
will find the corresponding item in the G column
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Public Utility 555" wrote
in
message ...
I have 8 columns and i need to look in column F1 thru F100 to find the
2nd
most recent date, and then pickup the corresponding number in column G
from
the same row. I came up with the formula below but it doesn't seem to
work.
Should it be an "IF" statement instead? OR am I missing something in
my
formula

=VLOOKUP(MAXA('45'!$F$4:$F$100,2),'45'!$G$4:$G$100 ,7,'45'!$G$4:$G$100)