VLookup with wildcard

[orejas]

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

 

[Shane Devenshire]

Hi,

You can use something like

=VLOOKUP("*"&E7&"*";Accountmanagers!$A:$B;2;TRUE)

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Shane Devenshire]

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

 

[T. Valko]

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)

 

[orejas]

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.

 

[T. Valko]

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

 

[T. Valko]

=VLOOKUP(--LEFT(C1,4),A1:B5,2,0)

Or, using semicolons as separators:

=VLOOKUP(--LEFT(C1;4);A1:B5;2;0)