Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nest vlookup within large function | Excel Worksheet Functions | |||
VLOOKUP in large Data sets of more than 16384 rows | Excel Worksheet Functions | |||
vlookup on large text in cells | Excel Worksheet Functions | |||
Vlookup in large named range | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions |