Formula to Identify Two Values in Adjacent Cells

andybason

Board Regular
Joined
Jan 7, 2012
Messages
197
Office Version
  1. 2016
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,967
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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,967
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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,210
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,836
Messages
5,833,905
Members
430,242
Latest member
Bancam

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