Thread: 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!

Originally Posted by Miraun

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!