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'.

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'.

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'.

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'.

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'.

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'.

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'.