vlookup functions using array

[ganeshpoojary05]

Hi all, can anyone help me with vlookup function, for example i have a data in column A with EMP ID's(with lots of spaces) and Column B contains their name. I need a formula where I can a return a name against the emp id(with no spaces).

Thanks!!!!

 

[MrKowz]

Welcome to the forums!

Try (assuming the empid you are looking up is in C1):

=INDEX(B1:B1000,MATCH(C1,SUBSTITUTE(A1:A1000," ",""),0))

Confirm entry with CTRL+SHIFT+ENTER to store the formula as an array. You will know it is entered correctly when you see {brackets} around the formula.

 

[ganeshpoojary05]

Thanks, Its working...