Getting lowest positions value in a list depending upon an index [SOLVED]

[kumargaurav]

Here is the data i have:

A1=1 B1=P

A2=2 B2=Q

A3=1 B3=R

A4=3 B4=S

A5=2 B5=T


C1=2


Now i want to fetch 1ST record from column B based upon an index value in A depending upon value in C1.

e.g. for C1=2,i should get

E1=Q ,i should not get T here

e.g. for C1=3,i should get

E1=S


This is the formula i have worked so far,but its simply returning the 1st record(i.e. P).

Senior members,could you pls help in correcting it.


{=INDEX(B1:B5,SMALL(IF(A1:A5=C1,A1:A5-A1+1),1))}

 

[Debraj]

Hi Gaurav,


above Index match formula is solution.. if VLOOKUP fails to provide multiple answer except 1st One..


So why you are using Solution.. when Vlookup is passed with full marks..

=VLOOKUP(C1,$A$1:$B$5,2,0)

Regards,

Deb

 

[Debraj]

Hi Kumar,


Thanks for blank post ..


just in case if you want to post further.. try below.

{=INDEX(B1:B5,SMALL(IF(A1:A5=C1,ROW(A1:A5)-ROW(A1)+1),1))}

Regards,

Deb

 

[kumargaurav]

thanks man...much appreciated...