Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup with wildcard
Hi,
I would like to use VLookup with a wildcard, but dont know how to. This is the formula I'm currently using, but which is not working. =VLOOKUP(E7;Accountmanagers!$A:$B;2;TRUE) The thing is, the result of E7 is (eg.) 1185TB, wheres Accountmanagers!$A:$B will contain 1185. So the question is how I look for 1185 (no letters) in 'Accountmanagers'. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup with wildcard
Hi,
You can use something like =VLOOKUP("*"&E7&"*";Accountmanagers!$A:$B;2;TRUE) If this helps, please click the Yes button Cheers, Shane Devenshire "orejas" wrote: Hi, I would like to use VLookup with a wildcard, but dont know how to. This is the formula I'm currently using, but which is not working. =VLOOKUP(E7;Accountmanagers!$A:$B;2;TRUE) The thing is, the result of E7 is (eg.) 1185TB, wheres Accountmanagers!$A:$B will contain 1185. So the question is how I look for 1185 (no letters) in 'Accountmanagers'. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup with wildcard
Hi,
I should add that you can use all three wildcards with VLOOKUP - *, ?, and ~ You can also mix an match them so this is a legal search "??E*" - it looks for anything with an E in the fourth postion. Finally you can put the wildcard into the VLOOKUP as I did or into the spreadsheet cell, E7. In the spreadsheet cell you would use A12B* without quotes. If this helps, please click the Yes button. Cheers, Shane Devenshire "orejas" wrote: Hi, I would like to use VLookup with a wildcard, but dont know how to. This is the formula I'm currently using, but which is not working. =VLOOKUP(E7;Accountmanagers!$A:$B;2;TRUE) The thing is, the result of E7 is (eg.) 1185TB, wheres Accountmanagers!$A:$B will contain 1185. So the question is how I look for 1185 (no letters) in 'Accountmanagers'. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup with wildcard
I think you have it backwards.
The lookup value is 1185TB The lookup table is 1185 Not enough detail for anything other than a "best guess" which might work. Maybe something like this: =VLOOKUP(--LEFT(E7;4);Accountmanagers!$A:$B;2;TRUE) -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, You can use something like =VLOOKUP("*"&E7&"*";Accountmanagers!$A:$B;2;TRUE) If this helps, please click the Yes button Cheers, Shane Devenshire "orejas" wrote: Hi, I would like to use VLookup with a wildcard, but dont know how to. This is the formula I'm currently using, but which is not working. =VLOOKUP(E7;Accountmanagers!$A:$B;2;TRUE) The thing is, the result of E7 is (eg.) 1185TB, wheres Accountmanagers!$A:$B will contain 1185. So the question is how I look for 1185 (no letters) in 'Accountmanagers'. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup with wildcard
Hi,
Thanks for both your replies! Very much appreciated. Let me try to clarify things. Certain Accountmanagers in our organisation have been assigned certain number-ranges. These numberranges and their accountmanagers can be found in 'Accountmanagers!', eg. 1185 relates to John Smith. Now, every client (lead) has a certain specific number, but with two added letters, eg 1185TB. This can be found in the 'E' column. Now what I'd like to do is look up what accountmanager should be assigned to a certain client. For example, what accountmanager should be assigned to a client with this number; 1185TB? I'd manually have to look up 1185 and see the relevant accountmanager and copy that. Thanks a binch in advance. "Shane Devenshire" wrote: Hi, I should add that you can use all three wildcards with VLOOKUP - *, ?, and ~ You can also mix an match them so this is a legal search "??E*" - it looks for anything with an E in the fourth postion. Finally you can put the wildcard into the VLOOKUP as I did or into the spreadsheet cell, E7. In the spreadsheet cell you would use A12B* without quotes. If this helps, please click the Yes button. Cheers, Shane Devenshire "orejas" wrote: Hi, I would like to use VLookup with a wildcard, but dont know how to. This is the formula I'm currently using, but which is not working. =VLOOKUP(E7;Accountmanagers!$A:$B;2;TRUE) The thing is, the result of E7 is (eg.) 1185TB, wheres Accountmanagers!$A:$B will contain 1185. So the question is how I look for 1185 (no letters) in 'Accountmanagers'. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup with wildcard
What I suggested does what you want as long as each number is 4 digits and
doesn't have any leading zeros. ...........A..........B 1.....1182.....Joe 2.....1183.....Sam 3.....1184.....Lisa 4.....1185.....Tom 5.....1186.....Sue C1 = 1185TB =VLOOKUP(--LEFT(C1,4),A1:B5,2,0) Returns: Tom -- Biff Microsoft Excel MVP "orejas" wrote in message ... Hi, Thanks for both your replies! Very much appreciated. Let me try to clarify things. Certain Accountmanagers in our organisation have been assigned certain number-ranges. These numberranges and their accountmanagers can be found in 'Accountmanagers!', eg. 1185 relates to John Smith. Now, every client (lead) has a certain specific number, but with two added letters, eg 1185TB. This can be found in the 'E' column. Now what I'd like to do is look up what accountmanager should be assigned to a certain client. For example, what accountmanager should be assigned to a client with this number; 1185TB? I'd manually have to look up 1185 and see the relevant accountmanager and copy that. Thanks a binch in advance. "Shane Devenshire" wrote: Hi, I should add that you can use all three wildcards with VLOOKUP - *, ?, and ~ You can also mix an match them so this is a legal search "??E*" - it looks for anything with an E in the fourth postion. Finally you can put the wildcard into the VLOOKUP as I did or into the spreadsheet cell, E7. In the spreadsheet cell you would use A12B* without quotes. If this helps, please click the Yes button. Cheers, Shane Devenshire "orejas" wrote: Hi, I would like to use VLookup with a wildcard, but dont know how to. This is the formula I'm currently using, but which is not working. =VLOOKUP(E7;Accountmanagers!$A:$B;2;TRUE) The thing is, the result of E7 is (eg.) 1185TB, wheres Accountmanagers!$A:$B will contain 1185. So the question is how I look for 1185 (no letters) in 'Accountmanagers'. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup with wildcard
=VLOOKUP(--LEFT(C1,4),A1:B5,2,0)
Or, using semicolons as separators: =VLOOKUP(--LEFT(C1;4);A1:B5;2;0) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... What I suggested does what you want as long as each number is 4 digits and doesn't have any leading zeros. ..........A..........B 1.....1182.....Joe 2.....1183.....Sam 3.....1184.....Lisa 4.....1185.....Tom 5.....1186.....Sue C1 = 1185TB =VLOOKUP(--LEFT(C1,4),A1:B5,2,0) Returns: Tom -- Biff Microsoft Excel MVP "orejas" wrote in message ... Hi, Thanks for both your replies! Very much appreciated. Let me try to clarify things. Certain Accountmanagers in our organisation have been assigned certain number-ranges. These numberranges and their accountmanagers can be found in 'Accountmanagers!', eg. 1185 relates to John Smith. Now, every client (lead) has a certain specific number, but with two added letters, eg 1185TB. This can be found in the 'E' column. Now what I'd like to do is look up what accountmanager should be assigned to a certain client. For example, what accountmanager should be assigned to a client with this number; 1185TB? I'd manually have to look up 1185 and see the relevant accountmanager and copy that. Thanks a binch in advance. "Shane Devenshire" wrote: Hi, I should add that you can use all three wildcards with VLOOKUP - *, ?, and ~ You can also mix an match them so this is a legal search "??E*" - it looks for anything with an E in the fourth postion. Finally you can put the wildcard into the VLOOKUP as I did or into the spreadsheet cell, E7. In the spreadsheet cell you would use A12B* without quotes. If this helps, please click the Yes button. Cheers, Shane Devenshire "orejas" wrote: Hi, I would like to use VLookup with a wildcard, but dont know how to. This is the formula I'm currently using, but which is not working. =VLOOKUP(E7;Accountmanagers!$A:$B;2;TRUE) The thing is, the result of E7 is (eg.) 1185TB, wheres Accountmanagers!$A:$B will contain 1185. So the question is how I look for 1185 (no letters) in 'Accountmanagers'. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with wildcard | Excel Worksheet Functions | |||
using wildcard for vlookup? | Excel Discussion (Misc queries) | |||
wildcard in formula vlookup | Excel Discussion (Misc queries) | |||
Wildcard vlookup perhaps? | Excel Discussion (Misc queries) | |||
Vlookup with wildcard | Excel Discussion (Misc queries) |