Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula problems
Hi, I am trying to use vlookup to get some figures out of a table of
data. A simplified version is as below: Fund G/N_Ind 1m_cum SB058 N* -1.01 46385 G -0.86 46385 N* -0.94 FC237 N* -1 If I use a simple vlookup to obtain the figures in the third column I can use the following formula: =VLOOKUP(A2,$A$2:$C$5,3,FALSE) However, as there is a repeat of the fund code in the first colum, when I want to return the figure -0.94 for the fund number 46385, it instead returns the first occurence of the fund and gives me the figure -0.86. Is there any other formula I can use to ask for the second instance corresponding to Fund 46385 or perhaps a formula that first looks for 46385 and then looks for N* so it will return the -0.94? Thanks, Simon |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula problems
Hi Simon,
Try this:- =INDEX($A$2:$C$20,SMALL(IF($A$2:$C$20=$D$1,ROW($A$ 2:$C$20)-ROW($A$2)+1,ROW($C$20)+1),2),3) The range for this is A2 to C20. The lookup value is in D1. The end 3 tells it to look in column 3 and the last 2 tells it to find the second instance. It can be altered to find the 3rd etc. It's an array so Ctrl+shift+enter. Mike "Simon888" wrote: Hi, I am trying to use vlookup to get some figures out of a table of data. A simplified version is as below: Fund G/N_Ind 1m_cum SB058 N* -1.01 46385 G -0.86 46385 N* -0.94 FC237 N* -1 If I use a simple vlookup to obtain the figures in the third column I can use the following formula: =VLOOKUP(A2,$A$2:$C$5,3,FALSE) However, as there is a repeat of the fund code in the first colum, when I want to return the figure -0.94 for the fund number 46385, it instead returns the first occurence of the fund and gives me the figure -0.86. Is there any other formula I can use to ask for the second instance corresponding to Fund 46385 or perhaps a formula that first looks for 46385 and then looks for N* so it will return the -0.94? Thanks, Simon |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula problems
Magic, thanks Mike! I have somehow managed to fix it into my much
larger and more complicated spreadsheet. That's great, thanks again! Simon |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula problems
Change "FALSE" to "TRUE" of course no quotes
"Simon888" wrote: Hi, I am trying to use vlookup to get some figures out of a table of data. A simplified version is as below: Fund G/N_Ind 1m_cum SB058 N* -1.01 46385 G -0.86 46385 N* -0.94 FC237 N* -1 If I use a simple vlookup to obtain the figures in the third column I can use the following formula: =VLOOKUP(A2,$A$2:$C$5,3,FALSE) However, as there is a repeat of the fund code in the first colum, when I want to return the figure -0.94 for the fund number 46385, it instead returns the first occurence of the fund and gives me the figure -0.86. Is there any other formula I can use to ask for the second instance corresponding to Fund 46385 or perhaps a formula that first looks for 46385 and then looks for N* so it will return the -0.94? Thanks, Simon |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula problems
Hi,
here is an alternate approach. You can create a column to the left of the data table which looks like this =A2&B2 This assumes that the first row of data is on row 2 and that the Fund # is in column A and the G/N in column B, Then your formula would read: =VLOOKUP(F1,$A$2:$D$5,4,FALSE) and in cell F1 you would enter 46385N* I have moved you lookup value from A2 to F1 in your original formula. -- Cheers, Shane Devenshire "Simon888" wrote: Hi, I am trying to use vlookup to get some figures out of a table of data. A simplified version is as below: Fund G/N_Ind 1m_cum SB058 N* -1.01 46385 G -0.86 46385 N* -0.94 FC237 N* -1 If I use a simple vlookup to obtain the figures in the third column I can use the following formula: =VLOOKUP(A2,$A$2:$C$5,3,FALSE) However, as there is a repeat of the fund code in the first colum, when I want to return the figure -0.94 for the fund number 46385, it instead returns the first occurence of the fund and gives me the figure -0.86. Is there any other formula I can use to ask for the second instance corresponding to Fund 46385 or perhaps a formula that first looks for 46385 and then looks for N* so it will return the -0.94? Thanks, Simon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup problems... | Excel Worksheet Functions | |||
VLOOKUP Problems | Excel Worksheet Functions | |||
VLOOKUP Problems | Excel Discussion (Misc queries) | |||
VLookup Problems | Excel Worksheet Functions | |||
Problems with the VLOOKUP formula | Excel Worksheet Functions |