Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
Hi
I have created a VLookup formula which returns the information I require it to. However, I need it to go a step further. For instance. I have asked it to look at the information in one column and show me the information which matches that in another column, everything which has an N/A obviously means there is no match or the information is not available. However, when I check the information that is supposedly incorrect, it is something minor like an extra character e.g. # or < which makes no difference to what I am trying to achieve. What I need help with is how to check this information but ingore those extra characters in the results. I have over 28,000 records which I have managed to reduce to 1,000 using the VLookup formula, just need that extra step to reduce the list even more. Any help would be greatly appreciated Here is the formula I am using: =VLOOKUP($A11365,Sheet1!$F11471:$F36016,1,FALSE) Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
Hi,
what you can do is to replace that charecters, highlight the column hit CTRL + H find what enter #, replace with leave as it and hit replace, do the same with < "Arsenal Lady 09" wrote: Hi I have created a VLookup formula which returns the information I require it to. However, I need it to go a step further. For instance. I have asked it to look at the information in one column and show me the information which matches that in another column, everything which has an N/A obviously means there is no match or the information is not available. However, when I check the information that is supposedly incorrect, it is something minor like an extra character e.g. # or < which makes no difference to what I am trying to achieve. What I need help with is how to check this information but ingore those extra characters in the results. I have over 28,000 records which I have managed to reduce to 1,000 using the VLookup formula, just need that extra step to reduce the list even more. Any help would be greatly appreciated Here is the formula I am using: =VLOOKUP($A11365,Sheet1!$F11471:$F36016,1,FALSE) Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
Here is the formula I am using:
=VLOOKUP($A11365,Sheet1!$F11471:$F36016,1,FALSE ) Assuming those extra characters are at the beginning or the end of the string. For example, you want to lookup "apple" but in your data table it may be listed as "#apple" or "#apple<" or "<apple" or "apple#" See if this does what you want: =IF(COUNTIF(Sheet1!F11471:F36016,"*"&A11365&"*"),A 11365,"NA") -- Biff Microsoft Excel MVP "Arsenal Lady 09" wrote in message ... Hi I have created a VLookup formula which returns the information I require it to. However, I need it to go a step further. For instance. I have asked it to look at the information in one column and show me the information which matches that in another column, everything which has an N/A obviously means there is no match or the information is not available. However, when I check the information that is supposedly incorrect, it is something minor like an extra character e.g. # or < which makes no difference to what I am trying to achieve. What I need help with is how to check this information but ingore those extra characters in the results. I have over 28,000 records which I have managed to reduce to 1,000 using the VLookup formula, just need that extra step to reduce the list even more. Any help would be greatly appreciated Here is the formula I am using: =VLOOKUP($A11365,Sheet1!$F11471:$F36016,1,FALSE) Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel |