Vlook up second value

[ananthram]

Hi Excel Ninjas,


i am using the below formula for a sheet but when the

lookup_value has same value again and the next column has different value its taking the 1st value into prior. please help me with this.


[code]=VLOOKUP($A2,[mm.xlsx]Sheet1!$D:$O,2,0)

[pre]Product code Product keyword
TIRP - 440 PTT
TIRP - 441 PTT
TIRP - 442 PTT
TIRP - 443 PTT
TIRP - 444 PTT
TIRP - 445 PTT
TIRP - 446 PTT
RCP 156 PTT
TIRP - 447 PTT
TIRP - 448 PTT
MCP 188 POTT
TIRP - 440 POTT
TIRP - 441 POTT
TIRP - 442 POTT
TIRP - 443 POTT
TIRP - 444 POTT
TIRP - 445 POTT
TIRP - 446 POTT[/code][/pre]

 

[keymaster]

See this please:


http://chandoo.org/wp/2010/11/10/vlookup-second-value/

 

[ananthram]

ha-ah as expected i have got the guidance from key master ty sir...

 

[ananthram]

but sir is there anyway like without helper column or without the second value column

i need it all in the same i.e.

Product keyword

column. please help the needful.

 

[sgmpatnaik]

@ANANTHRAM


HI


PLEASE CHANGE THE FORMULA TO


=VLOOKUP($A2,[mm.xlsx]Sheet1!$D:$O,2,0)


NE FORMULA


=VLOOKUP(A2,[mm.xlsx]Sheet1!D2:O1000,2,0)


HOPE IT WILL SOLVE YOUR PROBLEM IF NOT THEN PLEASE REPLAY


THANKS


SP

 

[Debraj]

Hi Ananthram,


If you data is in below format.. You can use Luke' Formula

formula forensics 003


With a little tweak. in Column F..

{=INDEX(Code,SMALL(IF(Product=$E2,ROW(Code)-1),COUNTIF($E$2:E2,E2)))}

with [b]Ctrl+Shift+Enter[/b]

[pre]A       B       C               E       F
Product	Code                    Product	Code
TIRP	440	PTT		TIRP	440
TIRP	441	PTT		RCP	156
TIRP	442	PTT		TIRP	441
TIRP	443	PTT		MCP	188
TIRP	444	PTT		TIRP	442
TIRP	445	PTT		TIRP	443
TIRP	446	PTT
RCP	156	PTT
TIRP	447	PTT
TIRP	448	PTT
MCP	188	POTT
TIRP	440	POTT
TIRP	441	POTT
TIRP	442	POTT
TIRP	443	POTT
TIRP	444	POTT
TIRP	445	POTT
TIRP	446	POTT

[/pre]

Where Product = A2:A19

and Code = B2:B19..


Change as per requirement..


https://dl.dropbox.com/u/78831150/Excel/Vlookup%20Second%20Value%28Ananthram%29.xls


Regards,

Deb

 

[ananthram]

@sgmpatnaik


awesome ty a lot... u really saved my day ty

 

[sgmpatnaik]

@Ananthram


You are welcome


SP