VLOOKUP to match substring
 

Hi All

Employee No.......................... Place
99440,994403,994404.............London
99442,994401,994405.............Manchester
994410..................................Leeds
994502..................................York

In above example hpw to vlookup part of string?
Each cell has multiple employee no. but i want to lookup single employee no
and it should return place.

e.g. If in above example if i vlookup "994403" then it should return London

Can I do this?

vlookup function returning appropriate value for '994410' & '994502' but not
the rest!!

I tried FIND, SEARCH functions but these to functions are for single cell
and not for array... :(:(

Thanks!

Answer: VLOOKUP to match substring
 

Yes, you can use the VLOOKUP function to match a substring. Here's how:

1. First, make sure that your data is organized in a table with two columns: Employee No. and Place. In your example, it looks like you already have this set up.
2. In a new cell, enter the employee number you want to look up. For example, if you want to look up "994403", enter that in a cell.
3. In another cell, use the following formula to perform the VLOOKUP:
   
   
   Formula:

   ---

   =VLOOKUP("*"&A1&"*",Table1,2,FALSE) 


   ---

   In this formula, replace "A1" with the cell containing the employee number you want to look up, and replace "Table1" with the name of your table.
   
   The "*" characters before and after the cell reference are wildcards that tell Excel to look for any text that contains the employee number. This allows you to match substrings within the cells.
   
   The "2" in the formula tells Excel to return the value from the second column of your table (the Place column).
   
   The "FALSE" at the end of the formula tells Excel to perform an exact match. This means that it will only return a result if it finds an exact match for the employee number you entered.
4. Press Enter to perform the VLOOKUP. The formula should return the corresponding Place for the employee number you entered.

That's it! You can now use this formula to look up any employee number in your table, even if it appears as part of a larger string.

VLOOKUP to match substring
 

suppose col A has employee no. and col B has place,

In D1 has employee no & in E1 put this formula

=INDEX(B1:B5,MATCH("*"&D1&"*",A1:A5,0),0)



On Oct 23, 4:44*pm, Milind Keer
wrote:
Hi All

Employee No.......................... Place
99440,994403,994404.............London
99442,994401,994405.............Manchester
994410..................................Leeds
994502..................................York

In above example hpw to vlookup part of string?
Each cell has multiple employee no. but i want to lookup single employee no
and it should return place.

e.g. If in above example if i vlookup "994403" then it should return London

Can I do this?

vlookup function returning appropriate value for '994410' & '994502' but not
the rest!!

I tried FIND, SEARCH functions but these to functions are for single cell
and not for array... :(:(

Thanks!

VLOOKUP to match substring
 

That is very good. With a little experimentation I have discovered that this
formula also works:

=VLOOKUP("*" & D1 & "*",$A$1:$B$5,2,FALSE)