Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 46
Default 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!

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 747
Default 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!


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 342
Default 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)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup retunrning a match, when not a match... Dave Peterson Excel Worksheet Functions 1 October 2nd 08 11:22 PM
vlookup retunrning a match, when not a match... mark Excel Worksheet Functions 4 October 2nd 08 10:39 PM
vlookup retunrning a match, when not a match... Niek Otten Excel Worksheet Functions 0 October 2nd 08 09:00 PM
index Match, or Vlookup Match.. news.transedge.com Excel Worksheet Functions 1 August 3rd 07 02:00 AM
Vlookup using a substring for evaluation? frosterrj Excel Worksheet Functions 6 December 22nd 04 02:23 AM


All times are GMT +1. The time now is 11:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"