Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an Excel worksheet with two sheets and two columns each with the below input:

sheet 1 data:

column1                                         column2
-------                                         -------


sheet 2 data:

  column1                                         column2
  -------                                         -------
  good_1                                           orange
  test_2                                           apple

I want the output of sheet 1 to look like below:

column1                                     column2
-------                                     -------
/a/b/c/d/                     orange
/d/e/f/g/test_2_xyz_pqr.pset                apple
/a/b/g/d/                    orange
/d/e/h/g/test_2_xyz_pqr.pset                apple
share|improve this question

migrated from Jun 10 '11 at 21:55

This question came from our site for professional and enthusiast programmers.

up vote 1 down vote accepted

The following will work for any path length, i.e. not just things like /a/b/c/d/test_2_xyz_pqr.pset but also things like /pump/up/the/jam/pump/it/up/test_2_xyz_pqr.pset. It extracts the first 6-character string after the last /, and looks it up in the Sheet 2 table.

Paste this into cell B1 and drag down:

    -LEN(SUBSTITUTE(A1,"/",""))))+1,6), Sheet2!A:B, 2, FALSE)

This assumes your "Sheet 1 column 1" data starts at A1, and your "Sheet 2" data is in columns A and B (preferably with nothing else in those columns).

Also, it assumes that only 6-character strings (such as test_4) are in the look-up table. If you want to consider strings of varying lengths, then the answer will be a good deal more complicated.

share|improve this answer
Hi Jean thank you for your solution. – srihari Jun 9 '11 at 14:46
Please tell me for the strings of varying lengths,say in the 2nd sheet the 1st column contains values of different lenghts Ex: abc_def or lets_do_this_better or abc_def_ghi like that all the strings are of different length,is there a way we can pick up the string match whether this string exists in the column1 of sheet 1 if it exists, in column 2 put the value associated with this string in sheet 2 ,2nd column. – srihari Jun 9 '11 at 14:51
As mentioned in my answer, this changes the solution completely. This is too long to answer in a comment. I suggest you upvote/accept the useful answers and post a new question. – Jean-François Corbett Jun 9 '11 at 16:49
@srihari I would suggest accepting what you think is the most useful answer in this thread and post another question, OR editing your question to include the new information you mentioned (e.g. the varying file/folder name lengths) – Kaze Jun 11 '11 at 4:17

For your exact situation described, try this in column 2 and drag down (make sure to change the cells or columns as needed):

=VLOOKUP(MID$($A2,10,6), Sheet2!A:B, 2, False)
share|improve this answer
Thanks Lance for answering,i just gave sample data here in sheet2 column1 i have values of different lengths,my requirement is if those values are present in the spread sheet i need to place the column 2 value of sheet 2 associated with this value in column 2 of sheet 1. – srihari Jun 9 '11 at 15:00
@srihari, so now you know how to get the data, you just have to create the filter you need to feed the VLOOKUP function. No one here can know the pattern that your fields will follow, but it should be simple for you to come up with your filter function. (and note, your question wasn't about that, it was just about mapping between sheets). – Lance Roberts Jun 9 '11 at 15:01

If you can use a user defined function. This is adapted from the function at superuser by @Hidden Koala

   Option Compare Text
   Public Function RETURNTEXT(src As Range, crt As Range, col as Integer) As String
   s = Trim(src.Value)
   For Each c In crt
    If InStr(1, s, Trim(c.Value)) Then newstr = WorksheetFunction.VLookup(Trim(c.Value), crt, col, False)
    Next c
    RETURNTEXT = newstr
    End Function

The range definining the strings to look for and return are in this sample at Sheet2!$A$1:$B$2

...added a column to return, to make this more versatile similar to the vlookup function

The function then you enter as =RETURNTEXT(A3,Sheet2!$A$1:$B$2,2)

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .