Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup - can't figure out what's wrong | Excel Worksheet Functions | |||
Office Assistance: VLOOKUP, Example 3- Row headers 1-7 are wrong? | Excel Worksheet Functions | |||
vlookup, what am I doing wrong? | Excel Discussion (Misc queries) | |||
VLOOKUP returning wrong row | Excel Worksheet Functions | |||
What wrong with VLOOKUP formula | Excel Worksheet Functions |