Excel Vlookup by partial value?

Question

Ok, so I have a list of serial numbers in one column, the first one or two numbers of every serial relate to information on another page; every serial number starting with 2 is a certain type of truck, every serial number starting with 16 is another type of truck. (EX: 16483 = 16, 2005 = 2). I just want the lookup to match the first one or two digits of hte cell value to the lookup information.

My formula looks like:

=IF(ISERROR(VLOOKUP(J2,Sheet1!$G$10:$J$28,3,FALSE)),"",VLOOKUP(J2,Sheet1!$G$10:$J$28,3,FALSE)*(Sheet2!F2))

I haven't been able to successfully use a wildcard to pick these up. Can anyone help?

Thanks

Answer 1

I tried these answers in 2003 and none of them worked. What I would suggest you do is to do the left(j2,?) where the ? collects the characters you want.

So a1 = source
b1 = left(source,?)
c1 = vlookup(reference cell,array,b1[which is the column return reference],false)

Just my 2 cents.

Answer 2

1

Answer 3

Get a No Cost Background Check Scan at https://tinyurl.im/aH3VT

Its a sensible way to start. The site allows you to do a no cost scan simply to find out if any sort of data is in existence. A smaller analysis is done without cost. To get a detailed report its a modest payment.

You may not realize how many good reasons there are to try and find out more about the people around you. After all, whether you're talking about new friends, employees, doctors, caretakers for elderly family members, or even significant others, you, as a citizen, have a right to know whether the people you surround yourself with are who they say they are. This goes double in any situation that involves your children, which not only includes teachers and babysitters, but also scout masters, little league coaches and others. Bottom line, if you want to find out more about someone, you should perform a background check.

Answer 4

Here is the most useful system verify people online by conducting an instant online background check. Reports may include Criminal Records, Addresses, Phone Numbers, Social Profiles, Court Records, Marriage and Divorce Records, Relatives, Email Addresses, Property Records and more!

Its a good method to begin for you: http://everified.biz/trust-1689.html
The web page will let you have a totally free scan in order to check if any kind of information is available. A small report is conducted totally free. For a in depth record its a tiny payment.

You Want Know:
• Want to know who is Calling?
• Need to find someone based on their Address?
• Find anyone by their Name, Email address?
• Considering a Criminal Background check online?
• ...

eVerify is exactly what you need! - http://everified.biz/trust-1689.html

Access one of the largest email databases on the internet and uncover someone’s name and address based only on their email address!

eVerify can help you uncover the identity of the person behind the phone number.

Use Criminal Records Search to uncover all that you can about someone's past. Learn about an individual’s criminal history and find out the details about their prior offenses. Find out if someone close to you has been convicted of a serious offense such as sex crime and more.

With websites available online to let you know about the background check of a person, phone lookup, criminal records,.. Everything you need about anyone or anything! eVerify is detective company usually do background checks. They reputable that has been around since 1999 and they never had any issues with that site from the last few years. I think you should try them: http://everified.biz/trust-1689.html

You can search for 4 informative search options: People, Background, Criminal Records, and Social Media. Just one in any of the information: Name, Phone Number, Address, Email, ...

You are only a few easy steps away from uncovering the truth about someone’s background!

If you come across a service that makes you believe that you can find the name of the owner of a certain cell phone number for free, I can only tell you that you will probably get information that is very unreliable.

Get ALL the ANYONE information You need Now! - http://everified.biz/trust-1689.html

So, it's better if you use a service like eVerify that requires you to pay a small fee, but at least you'll know that you will get the name you want in no time.

Answer 5

For the best answers, search on this site https://smarturl.im/aDCju

ok, this can be done using the "DGET" (database get) function of excel. The way this works is... put a column heading for A1 and B1...let's say Product / Price then list all your 7up, Stella, etc under these headings in cells C1 & D1 enter the same headings in cell C2 enter " *carling* " (w/o the double quotes) in cells C3 enter =DGET(A1:B100, "Price", C1:C2) the syntax of this formula is dget (range of your database, name of column heading you want to retrieve, your criteria range) if there is only one match to carling, it'll return the price. If there are multiple matches, it returns #NUM (because it doesn't know which one to get). the "criteria" we entered above uses the wildcard * to tell excel to find the text anywhere in the table. there are a bunch of other D.... type formulas (DAVERAGE, etc) that you can use as a database functions. For more help on using them just go to your help screen (F1) and look for DGET, etc. -----------more---------> No way to return multiple values. For your particular application, a database program like MS Access would make more sense than a spreadsheet.

Answer 6

2

Answer 7

If the serial number is always 3 numbers longer than what you want to look up, this formula will work for all of your numbers:

=IF(ISERROR(VLOOKUP( LEFT(J2,LEN(J2)-3), Sheet1!$G$10:$J$28, 3, FALSE)), "", VLOOKUP(LEFT(J2,LEN(J2)-3), Sheet1!$G$10:$J$28, 3, FALSE) * Sheet2!F2)

This finds the length of your serial number, and always ignores the last 3 digits.

If you need more info, add more detail or e-mail.
Cheers.
_____
EDIT: the above works if the numbers you look up (16 or 2 in your example) are in text format, and that may be what Frank is referring to. If they're in number format, then you need to replace
LEFT(J2,LEN(J2)-3)
with
VALUE(LEFT(J2,LEN(J2)-3))
.

Answer 8

Can you add another column to the sheet in this column do a substring (it is the mid function in VBA) to get the first character or first 2 characters from the serial number column and use your Vlookup based on this new column ?

Answer 9

vlookup(left(j2,1),


Using "LEFT(J2,1)", you'll tell it you only want it to lookup the first character in cell J2.

Check out my related article:

http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel.htm

And check out the LEFT, MID, and RIGHT functions in the help files. :)