Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup
I am trying to create a lookup where I am entering a 10 account number digit
but my database only has 8 number digits. I want my lookup to ignore the last two digit I enter and give me the corresponding 8 digit number |
#2
|
|||
|
|||
Hi,
Lookup value is in e.g. A1: =VLOOKUP(LEFT(A1,8),Lookup_Table,1,0) Where Lookup_Table is the table or array you want to return the corresponding number from. Regards, A "Sara Gilbert" wrote: I am trying to create a lookup where I am entering a 10 account number digit but my database only has 8 number digits. I want my lookup to ignore the last two digit I enter and give me the corresponding 8 digit number |
#3
|
|||
|
|||
=vlookup(int(a1/100),yourtable,2,false)
If those values are numbers. =vlookup(left(a1,8),yourtable,2,false) if the values are text. Sara Gilbert wrote: I am trying to create a lookup where I am entering a 10 account number digit but my database only has 8 number digits. I want my lookup to ignore the last two digit I enter and give me the corresponding 8 digit number -- Dave Peterson |
#4
|
|||
|
|||
unfortunately gives me: #N/A
=VLOOKUP(LEFT(B13,8),Data,2) Data is my database info on another sheet "aristotle" wrote: Hi, Lookup value is in e.g. A1: =VLOOKUP(LEFT(A1,8),Lookup_Table,1,0) Where Lookup_Table is the table or array you want to return the corresponding number from. Regards, A "Sara Gilbert" wrote: I am trying to create a lookup where I am entering a 10 account number digit but my database only has 8 number digits. I want my lookup to ignore the last two digit I enter and give me the corresponding 8 digit number |
#5
|
|||
|
|||
Worked with the first formule.
Thanks a lot! "Dave Peterson" wrote: =vlookup(int(a1/100),yourtable,2,false) If those values are numbers. =vlookup(left(a1,8),yourtable,2,false) if the values are text. Sara Gilbert wrote: I am trying to create a lookup where I am entering a 10 account number digit but my database only has 8 number digits. I want my lookup to ignore the last two digit I enter and give me the corresponding 8 digit number -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Convert VLOOKUP to absolute cell reference | Excel Discussion (Misc queries) | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |