# Formula to Identify Two Values in Adjacent Cells

#### andybason

##### Board Regular
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

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
if they are exact matches then
=ABS(MATCH(AA5,Sheet1!A5:A54,0)-MATCH(AA6,Sheet1!A5:A54,0))=1

will work

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

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

Thank you all for your help

Replies
8
Views
444
Replies
3
Views
242
Replies
2
Views
208
Replies
0
Views
350
Replies
4
Views
285

1,219,574
Messages
6,149,082
Members
450,855
Latest member
onecodevee01

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back