Formula to Identify Two Values in Adjacent Cells

andybason

Board Regular
Joined
Jan 7, 2012
Messages
180
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
if they are exact matches then
=ABS(MATCH(AA5,Sheet1!A5:A54,0)-MATCH(AA6,Sheet1!A5:A54,0))=1

will work
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,560
Members
417,220
Latest member
lam150498

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top