+ Reply to Thread
Results 1 to 3 of 3

Cross-reference and matching text

  1. #1
    Registered User
    Join Date
    09-16-2011
    Location
    Lake Worth, FL
    MS-Off Ver
    Excel 2003
    Posts
    2

    Cross-reference and matching text

    Hey!

    This is my first time posting here so I hope I'm in the right area. My problem is like this:

    I have a list of sentences that contain a car licence plate number in, say, column A. Then i have a list of the licence plates in column C and in column D the car stock number. For example in C1 the plate ABC-123 and in D1 the number 1, C2 ABC-124 and D2 the number 2 etc.

    What I would need excel to help me with, is when I copy/paste a new list of sentences in column A, it would return me the stock number of the car in column B by finding it in columns C and D. How can this be done?

    Thank you a lot!

  2. #2
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: Cross-reference and matching text

    Yep, you're looking for a simple Vlookup.

    In B1, use something similar to the following equation:
    =Vlookup(A1,$C$1:$D$10000,2,False)

    Replacing the C1:D10000 array with your total population of known plates/cars.
    It's important that you anchor your reference data with the $ symbols. Otherwise when you copy down the equation in column B, the reference data will slide with it.
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  3. #3
    Registered User
    Join Date
    09-16-2011
    Location
    Lake Worth, FL
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Cross-reference and matching text

    Quote Originally Posted by Miraun View Post
    Yep, you're looking for a simple Vlookup.

    In B1, use something similar to the following equation:
    =Vlookup(A1,$C$1:$D$10000,2,False)

    Replacing the C1:D10000 array with your total population of known plates/cars.
    It's important that you anchor your reference data with the $ symbols. Otherwise when you copy down the equation in column B, the reference data will slide with it.
    Ok, thank you very much! This works nicely otherwise but the value in column A is in a sentence. So the column A looks sort of like this:

    A
    Vehicle license plate ABC-123 9/14 6:00 AM
    Vehicle license plate ABC-124 9/14 3:00 PM
    Vehicle license plate ABC-125 9/15 9:30 AM
    Vehicle license plate ABC-123 9/16 10:00 AM

    And I should find the match from there.

    Thanks for the quick answer by the way!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1