Vlookup derived wrong value
 

Formula used for LastMthCost is =vlookup(A2,LastMthCost!A2:B3,2,FALSE)
Instead of returning 1.5 but i got 2.5!!!!! Please help

Worksheet: ThisMthCost
A B
1 PartNum LastMthCost
2 M03541224*a 2.5

Worksheet: LastMthCost
A B
1 PartNum LastMthCost
2 M03541224**a 2.5
3 M03541224*a 1.5

Vlookup derived wrong value
 

The problem is that Excel is interpreting the * as a wildcard character.
Based on your limited sample this array formula** will work:

=INDEX(LastMthCost!B2:B3,MATCH(TRUE,EXACT(A2,LastM thCost!A2:A3),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Note that the EXACT function requires that a lilteral *exact* match must be
found. This means that it's case sensitive.

Using EXACT, these values will not match:

M03541224*a
M03541224*A
m03541224*a

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
Formula used for LastMthCost is =vlookup(A2,LastMthCost!A2:B3,2,FALSE)
Instead of returning 1.5 but i got 2.5!!!!! Please help

Worksheet: ThisMthCost
A B
1 PartNum LastMthCost
2 M03541224*a 2.5

Worksheet: LastMthCost
A B
1 PartNum LastMthCost
2 M03541224**a 2.5
3 M03541224*a 1.5

Vlookup derived wrong value
 

Excel supports wild cards (* and ?, any set of characters and any single
character).

The tilde is used to tell excel that you don't mean the wildcard--you actually
mean that character ~* and ~?.
Since tilde has a special purpose, you have to treat it special too: ~~.

And if your data has asterisks and question marks, you may want something like:

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~"," ~~"),"?","~?"),"*","~*"),
LastMthCost!A2:B3,2,FALSE)

Michelle wrote:

Formula used for LastMthCost is =vlookup(A2,LastMthCost!A2:B3,2,FALSE)
Instead of returning 1.5 but i got 2.5!!!!! Please help

Worksheet: ThisMthCost
A B
1 PartNum LastMthCost
2 M03541224*a 2.5

Worksheet: LastMthCost
A B
1 PartNum LastMthCost
2 M03541224**a 2.5
3 M03541224*a 1.5

--

Dave Peterson

Vlookup derived wrong value
 

Try this:
A5: is your criteria

=SUMPRODUCT(--(A1:A2=A5),B1:B2)


"Michelle" wrote:

Formula used for LastMthCost is =vlookup(A2,LastMthCost!A2:B3,2,FALSE)
Instead of returning 1.5 but i got 2.5!!!!! Please help

Worksheet: ThisMthCost
A B
1 PartNum LastMthCost
2 M03541224*a 2.5

Worksheet: LastMthCost
A B
1 PartNum LastMthCost
2 M03541224**a 2.5
3 M03541224*a 1.5

Vlookup derived wrong value
 

Lots of thanks for all your suggestions
I had tried this particular one and it seems like working fine.

"Dave Peterson" wrote:

Excel supports wild cards (* and ?, any set of characters and any single
character).

The tilde is used to tell excel that you don't mean the wildcard--you actually
mean that character ~* and ~?.
Since tilde has a special purpose, you have to treat it special too: ~~.

And if your data has asterisks and question marks, you may want something like:

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~"," ~~"),"?","~?"),"*","~*"),
LastMthCost!A2:B3,2,FALSE)

Michelle wrote:

Formula used for LastMthCost is =vlookup(A2,LastMthCost!A2:B3,2,FALSE)
Instead of returning 1.5 but i got 2.5!!!!! Please help

Worksheet: ThisMthCost
A B
1 PartNum LastMthCost
2 M03541224*a 2.5

Worksheet: LastMthCost
A B
1 PartNum LastMthCost
2 M03541224**a 2.5
3 M03541224*a 1.5

--

Dave Peterson