Vlookup

[Arsenal Lady 09]

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

 

[Eduardo]

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

 

[T. Valko]

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&"*"),A11365,"NA")