Vlookup or Index Formula

[Manikandan Arumugam]

Hi All,

I have an excel sheet with google LinkedIn results extracted data. I want to get cells which contains "|" and next to the hyperlink cells in another sheet. Attached the sample input and manually mocked up output file for your reference. Plz help me to sort it out.

Thanks in advance!

[shukla.ankur281190]

Ok Try in input Sheet

B2

Please Login or Register  to view this content.

C2

Please Login or Register  to view this content.

D2

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in output Sheet

A1

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Check the aatached file.

[Manikandan Arumugam]

Hi Shukla,

Thank you so much. I have 397 entries in input.

=INDEX(input!$A$2:$A$397,SMALL(IF(input!$D$2:$D$397=TRUE,ROW(input!$A$2:$A$397)-ROW(input!$A$1)),ROWS(A$1:A2)))

when i change formula like this it returns #NUM! error

What I have to change. Plz help me

[AliGW]

Try this:

=INDEX(input!$A$2:$A$397,SMALL(IF(input!$D$2:$D$397=TRUE,ROW(input!$A$2:$A$397)-ROW(input!$A$1)),ROWS(A$1:A1)))

[shukla.ankur281190]

Do follow post 4# suggestion.

[Manikandan Arumugam]

Thanks Ali. Silly mistake I made

[AliGW]

No worries! Easily missed when dealing with long formulae.