Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell matching issue
Hi guys,
Ok here is my issue: I have a workbook containing 2 sheets. One sheet contains a list of ETFs with livefeed data from Bloomberg about two different volume measures.I then use this data to do a simple calculation for the performance of each ETF. The issue is the fact that in the other sheet I want a table with the top 5 performers and the worst 5 performers. What I did in order to receive the names of the ETFs, provider, performance etc, I used the VLOOKUP function. The performance is the column on the far left. =VLOOKUP(LARGE(data!$C$4:$C$41,1),data!$C$4:$L$41, 7,TRUE) data is the sheet with the names, volumes and performance The above function for me means that it should look for the 1st largest number within the performance column(C column) and once it finds it in finds the corresponding data in the same row but in column 7 which is where the ETF name is.The TRUE is used to make sure its a match value. For some reason it is not working and I just dont understand why. I just want to isolate the top 5 performers by performance and have all the corresponding ETF data in a table. Is this wrong? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell matching issue
On Wed, 1 Jul 2009 06:06:58 -0700 (PDT), totalnatal
wrote: Hi guys, Ok here is my issue: I have a workbook containing 2 sheets. One sheet contains a list of ETFs with livefeed data from Bloomberg about two different volume measures.I then use this data to do a simple calculation for the performance of each ETF. The issue is the fact that in the other sheet I want a table with the top 5 performers and the worst 5 performers. What I did in order to receive the names of the ETFs, provider, performance etc, I used the VLOOKUP function. The performance is the column on the far left. =VLOOKUP(LARGE(data!$C$4:$C$41,1),data!$C$4:$L$41 ,7,TRUE) data is the sheet with the names, volumes and performance The above function for me means that it should look for the 1st largest number within the performance column(C column) and once it finds it in finds the corresponding data in the same row but in column 7 which is where the ETF name is.The TRUE is used to make sure its a match value. For some reason it is not working and I just dont understand why. I just want to isolate the top 5 performers by performance and have all the corresponding ETF data in a table. Is this wrong? Thanks Change TRUE to FALSE if your data is not sorted. And make sure the data you want to get is i column I. Column I is the 7'th column if you start with column C. Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching and returning only one cell | Excel Discussion (Misc queries) | |||
find cell with matching text, then reference cell in same row | Excel Worksheet Functions | |||
Issue with Cell alignment - need only the last line in cell to sho | New Users to Excel | |||
Matching more than one cell | Excel Discussion (Misc queries) | |||
Matching data and linking it to the matching cell | Links and Linking in Excel |