I need a formula to get an exact value by vlookup detail is mentioned below
If Col C1=40658874, 40658880 and D1=P987006 E1= 40658874
I need a formula in F11 to get the value of D1 by matching E1
D1=Vlookup(E1,D:D,2,False) this formula is not working as C1 contains 2 values with a comma.
Note=c1 have 2 ot more than 2 values.
Thanks in advance for your help.
Regards
Waqas Munir
Hi Waqas
Try using wildcard when matching but make sure that the entire column to search is text and not a mixture of numbers and text.
Something like this :
=VLOOKUP("*"&C2&"*",A1:B4,2,FALSE)
Hope this helps.
Sunny
???????????
column have mixture of values text and numbers but if i can remove comma between to numbers from cell that is like 654987 6598874 instead of 654987, 659874 then can we convert all the column into numbers and can i have any formula to get the exact value???
more over the formula you mentioned have any error because if C1 gave zero value your given formula take the first value of array.
| A | B | C | D | |
| 40654468, 65498713 | 65541 | 65541 | ||
| 65498754 | 65987 | 65498754 | 65987 |
i need a formula that should work for single and multiple value of the cell as shown below
| 65498741 65987465 | 369874 |
| 65987456 98765474 | 149865 |
| 65987123 65875412 | 259874 |
| 65983214 | 653214 |
| 65783595 | 894532 |
Please suggest a vlookup that i enter in D1 like this =vlookup(C1,A:B,2,false)...now if C1 have 65498741 then D1 should be equal to 369874 which is matched in A1 as shown above and if C1 65783595 then D1 should be 894532 which is matched in A5 as shown above.
Thanks for your cooperation.
Waqas Munir
Try
=IFERROR(VLOOKUP("*"&C1&"*",$A$1:$B$5,2,FALSE),VLOOKUP(C1,$A$1:$B$5,2,FALSE))
