Formula to Identify Two Values in Adjacent Cells

[andybason]

Hello

I have values in cells AA5 and AA6 in Sheet1 and a list in cells A5 to A54 in Sheet3. I am trying to write a formula to return TRUE if cells AA5 and AA6 are adjacent to each other in the list (ie next to each other in the same column). As the contents of the list may not be an exact match I also need to use the "*"&AA5&"*" fuzzy search.

I cannot work out how to do this. Can anyone offer any advice?

Thank you

 

[sijpie]

if they are exact matches then
=ABS(MATCH(AA5,Sheet1!A5:A54,0)-MATCH(AA6,Sheet1!A5:A54,0))=1

will work

 

[sijpie]

Ok, they may not be perfect matches, but will they start with the same characters, or could the characters in AA5 bi in the middle of the text in one of the cells in the list?

If they start with the same then you can use LEFT() in the formula above.

Also have a look at these things, they are for fuzzyVLookup, but with some ingenuity the result of the fuzzyVLookup can be incorporated in the MATCH formula. This involves a UDF and thus macro
http://www.mrexcel.com/forum/excel-questions/479285-non-exact-text-look-up-excel.html

And then ther is the Fuzzylookup Add-in from Microsoft, see:
Performing Fuzzy Lookups in Excel

 

[Aladin Akyurek]

Hello

I have values in cells AA5 and AA6 in Sheet1 and a list in cells A5 to A54 in Sheet3. I am trying to write a formula to return TRUE if cells AA5 and AA6 are adjacent to each other in the list (ie next to each other in the same column). As the contents of the list may not be an exact match I also need to use the "*"&AA5&"*" fuzzy search.

I cannot work out how to do this. Can anyone offer any advice?

Thank you

Looks like...

=MATCH("*"&AA6&"*",Sheet3!A5:A54,0)-MATCH("*"&AA5&"*",Sheet3!A5:A54,0)=1

 

[andybason]

Thank you all for your help