Lookup Formula in excel

[James Carlo Cruz]

Hi Excel Ninjas,

Is there a formula in excel that could do a lookup for 1 reference but would have different values? I might be sound confusing but I have attached the file for you to see and check what do I mean.

 

[Naresh Yadav]

Hi,

Pls find the attached excel file
I have used helper column with formula (B2&"-"&COUNTIF($B$2:B2,B2) to convert your repeated lookup value in to a unique count and than used vlookup

regards
Naresh

 

[James Carlo Cruz]

Hi Naresh,

This is great, I think this is the one I need

 

[Deepak]

Array so Ctrl+Shift+Enter

B12=INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=A12,ROW($A$1:$A$6),""),ROW()-11))

& copy down.

 

[Khalid NGO]

Hi to all,

One more with just enter:

=INDEX($B$2:$B$6,AGGREGATE(15,6,(ROW($B$2:$B$6)-1)/($A$2:$S$6=A12), COUNTIF(A$12:A12,A12)))

You can replace the Countif part with Row(A1) if your lookup values are identical.

Regards,

 

[James Carlo Cruz]

Thank you all for the assistance, everything is working correctly. But I chose to use B12=INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=A12,ROW($A$1:$A$6),""),ROW()-11))

Everyone is so helpful in this site.

 

[bines53]

Hi Khalid NGO ,

Glad to see that you've started to work with the function,AGGREGATE !


David

 

[Khalid NGO]

Hi Khalid NGO ,

Glad to see that you've started to work with the function,AGGREGATE !


David

Hi David,
Thank you

Yes, AGGREGATE function is amazing, I have recently learned to replicate CSE formulas with this function.

Regards,