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

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

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

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

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