Comments on: Excel: check if two cells match or multiple cells are equal

An Excel formula to see if two cells match could be as simple as A1=B1. However, there may be different circumstances when this obvious solution won't work or produce results different from what you expected. Continue reading →

Comments page 2. Total comments: 190

  1. Do you have a formula for this condition : ( if cell1=x and cell2 y → cell3 = z) ? thank you .

  2. Hi There, I would love some help. I have two sheets with the same data range, State and Email. I am attempting to cross reference if the two sheets have matching information. I have used this formula but it is not working.

    =IF(AND(C2='Response Data'!B2,'Agent TMT State Data'!,D2='Response Data'!C2),TRUE

    1. Hi! Since your formula is not completely written down, and I do not have your data, I cannot verify it. 'Agent TMT State Data'!,D2 - mistake, no comma needed. C2 - there is no reference to the worksheet. Use the recommendations I gave earlier: Compare two columns for matches and differences. Or give an example of what you want to compare.

      1. Hi Alex,

        I have two worksheets named Response Data and Agent TMT Data. Each worksheet has a column for State and Email, there are roughly 6000 records.
        I want to create a formula that will give me a response of Match/No Match or True/False if the row that contains the data for state and email is not matching.

        EG: Response data has C2 test@test. com and D2 Texas, TMT Agent data has the same information in the worksheet (test @test. com and Texas in the same row)

        I hope this makes sense

        1. Hello! If I understand your task correctly, the following formula should work for you:

          =ISNUMBER(MATCH('Agent Response Data'!C2&'Agent Response Data'!D2, 'TMT State Data'!C2:C30&'TMT State Data'!D2:D30,0))

          We have a tool that can solve your task in a couple of clicks - Compare Two Tables. This tool can compare two tables or lists by any number of columns, identifying matches/differences (like formulas) and highlighting them (like conditional formatting). The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.

  3. Hi, I have a big excel sheet containing data on M&As which I have to combine.

    I have three columns which have to match, two columns with Ticker Symbols and one with the corresponding ISIN code of a company. If the ticker symbols match I want to get the corresponding ISIN code for the company and its deal (in some cases it is one deal, others could be 20). But for some reason it does not check all cells, rather if I write a function it automatically jumps to the next ticker symbol. Do you perhaps know what function to use?

    1. Hi! I can't guess which formula you're using. But if you're searching using VLOOKUP or INDEX MATCH, remember that these will only find the first match. If you want to get all the values from the third column where the values from the first and second columns match, use the FILTER function and these instructions: Excel FILTER function - dynamic filtering with formulas.
      Unfortunately, the information you have provided is not sufficient for us to recommend a formula to you.

  4. Can I get help with this formula? I'm getting a syntax error message when I enter this and can't figure out the issue. I just want it to pull a value based on different ranges.

    =IFS(COUNTIF(Reference!B2:B64,B3)>0,Reference!B1, [COUNTIF(Reference!C2:C64,B3)>0],Reference!C1, [COUNTIF(Reference!D2:D64,B3)>0],Reference!D1)

  5. I have two drop down list B2 and D2. If B2 equals to a text then D2 should only have a certain lot of values/text available to use and the rest must be grey out. what formula to i use?

  6. Hi,
    I have two different diagrams. What they have in common is that they both have one column with the same material number, but sorted in different rows.
    But, the first diagram have a column with the change of the quantities of how much materials will increase or decrease.
    And the other diagram have a cost per unit column.

    I must somehow figure out how much will the cost increase or decrease in every row for every material number.

    Maybe I can use the material number. If they match equally,then I can multiply the Change in Quantities with Cost per Unit. But how?

  7. Is there a way to make a formula , if any cell in one column match another cell so number should be taken from specific cell

    1. Hi! Based on your description, it is hard to completely understand your task. To get a value by condition, you can use the IF formula.
      If you want to search for a value in a column and, if it is found, get the value from another cell, use the MATCH function. The ISNUMBER function returns TRUE when the value is found. For example:

      =IF(ISNUMBER(MATCH(B2, A2:A11,0)),C2)

  8. I want to get a yes or no answer. If G3 equals H1 or I1 then no, otherwise yes

    Column G could have PP01, PP02, PP01A, PP02A. H1 = PP01, I1 = PP02

  9. First off, great simple instructions! Very helpful.

    Using this formula =A2=B2 (If two cells equal, return TRUE), is there a way to have True in Green color font, and False in Red color font.

  10. I want to display a stock closing price which will change daily in the same cell(A2). I want to compare with cell just to the right of it(B2) to see if the first cell(A2) is higher than the second cell(B2). If it is then copy A2 to B2, if not do nothing. Is this possible?

  11. I have 2 spreadsheets.

    1 has the correct supplier name, inv number and description
    1 has the wrong supplier name, but correct invoice number and description.

    Is there a way for me to move the correct supplier name into the second spreadsheet without having to it manually? the wrong supplier's are scattered across multiple rows so I cannot just copy and paste and it's over 5k of rows so very time consuming to do manually.

    1. Hello! Using the VLOOKUP or INDEX MATCH functions, you can use the invoice number to find the supplier's name in the second table and enter it in a separate column in the first table. But you cannot replace the wrong name with the correct name using formulas. Also, Excel has no way of knowing if it found the correct name.
      Therefore, I recommend you to check out the Fuzzy Duplicate Finder tool. The add-in searches for partial duplicates that differ by 1 to 10 characters. It also detects omitted, excess or mistyped symbols.
      The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.

  12. Hi,

    I'm using this formula currently: =IF(COUNTIF('Kansas City-CRs'!C:C,TAS!A5)>0,"YES","NO"), but it's returning YES if any portion of A5 is found in C:C. For instance in A5 if it says 'Very High Elevated LDL' but in C:C there is just 'Elevated LDL' it will return Yes. I want it to return YES only if it's an exact match. I tried doing match and index and exact formulas but can't get it to work. Any help would be much appreciated!!

    Theresa

    1. Hi! Your formula returns Yes only if there is a full match, not a partial match. With your data, the formula returns NO.

  13. Hello!

    I am trying to build a report for to display whether a code is at risk or not.

    I have a tab for Data and a tab for Overview. On each tab there is a column for code and a column for risk (Y/N). I am looking for a formula that will take the Y/N indicator that is entered for a specific code (XYZ) on the Data tab and automatically fill into the coinciding row for code XYZ under the risk column on the overview tab. The codes on the Data tab are not in the same order as the Overview tab. Is this possible?

    Please let me know if there is any additional information needed.

  14. I am putting together an attendance calendar for my staff.

    One tab has the calendar, and the 2nd tab has a table of call-in dates and reason codes. I want to use conditional formatting to color the calendar based off the date AND the reason code. I am able to color in the calendar based off JUST the date by using the formula =NOT(ISNA(MATCH(B10,$G$6:$G18,0))) but I cannot seem to add the secondary condition - the reason code. I have tried the AND function, the XLOOKUP function, the IF/IFS function etc. and as soon as I add a second condition all the formatting goes away.

    Please let me know if there is more information you need to better understand the problem I'm having.

  15. Would you please help?
    I have multiple columns with values of prices of products and some contain "#N/a". I used if and function to compare the prices in all columns and give me "No" if there was no change in price and "yes" if there was a change, but this does not work for columns with #N/a values as it gives me "yes" as if there was a change in price but in fact there is not. How can I get the result I want while ignoring #N/a?

    Also what formula can I use to know if the price changed to be higher vs lower?

  16. Seeking support for solving the bellow result

    C1=MOHAMMAD, Osman Goni
    D1=Osman Goni
    The person is same but during data collection mismatch the spelling or missing any part of the name.
    So need expected result in E1 'True'
    Would you please anyone help to solve this issues. Thanks.

  17. This is similar but not the same as the situation posted above.
    I have a workbook with tabs for each week ("Week 1", "Week 2", etc...) and listed in column E are names and column G are a Yes or No for goal setting. The names are not in order on each sheet (for reasons). I want to setup a Standings sheet that shows how many times each person met their goals for each week.
    I found I can do this with an individual sheet with the following formula, but I can't figure out how to get it to check all the sheets.
    =COUNTIFS('Week 1'!E:E,"Will",'Week 1'!G:G, "Y")

    Thanks for any help.

    1. Hi! If you want to find the sum of values on multiple worksheets, try these guidelines: Excel COUNTIF and COUNTIFS with OR logic. You can't in a COUNTIFS formula dynamically change the range to be counted.

      =COUNTIFS('Week 1'!E:E,"Will",'Week 1'!G:G, "Y") + COUNTIFS('Week 2'!E:E,"Will",'Week 2'!G:G, "Y")

  18. Hi there,

    I have 2 lists from the past 2 years that have email addresses of attendees for a course
    I need a formula that can make a list of emails that have been used both years to see if they would like to attend for the 3rd year running
    I have tried =IF(EXACT(H2:H970, R2:R1273), "Match", "Don't match" but obviously this only works if there are in the same order.
    One list has 970 emails the other has 1273.
    Ideally if it could produce a list of the emails that have occurred both years so then we can email that list rather than a true/false yes/no match/dont match.

    Hopefully that makes sense!
    Thank you

    1. Hi! If I understand your task correctly, this guide may be helpful: How to compare two columns in Excel for matches and differences. To mark in a separate column the values in column A that are in column B, use the formula:

      =IF(COUNTIF($B1:$B2000, $A1)>0, "Match", "")

      To get a list of such values, use the FILTER function:

      =UNIQUE(FILTER(A1:A2000,COUNTIF(B1:B2000,A1:A200)>0))

      Use the UNIQUE function to remove accidental duplicates.
      I'd recommend you to take a look at formula-free way to compare two columns in Excel. Compare Two Tables tool identify matches and differences in a couple of clicks. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

      1. The unique formula worked great!
        Thank you!

  19. Not sure if this will work. I have a list of learners and which training they took in 2023. I also have a column that shows the time spent on that training.
    I want to find the average time spent for each learner.

    So, I want to say, if the Learner is the same, calculate those duration columns (and then maybe calculate the average at the end)

    Learner A Training 1 0:04:00
    Learner A Training 2 0:13:00
    Learner A Training 3 0:20:00
    Learner B Training 1 0:14:00
    Learner B Training 2 0:09:00

    Thank you!

  20. Please could someone assist.. its not very complex however I am struggling.
    I am attempting to get excel to return a bonus amount in a specific cell if the 2 figures match the table

    So if C17 and D17 match the exact combination of the table S13:T27 then it should return the correct amount matched with the table U13:U27

    Ie if C17 states 400 D17 states 600
    The table would have variations of

    S T U
    NIL / 400 = Nil
    400/ 400 = 400
    400 / Nil = Nil
    400/ 600= 400
    600 / Nil = Nil
    600/ 400 = 400
    600/600 = 600

    It would need to return 400
    If nil/ 400 needs to return nil
    Etc

    Thank you in advance

  21. I am trying to figure out how to do this: If A2 and B2 combined of sheet 1 are the same as any instance in column A2:A2630 and B:1:AT:1 combined, then it will return the data found in cell C2 of sheet 1 in cell B2 of sheet 2.

    Sheet 1
    Code State Answer
    123456 Alabama Exempt
    123457 Alabama Taxable
    123458 Alabama Exempt
    123459 Alabama Exempt
    123460 Alabama Taxable
    123461 Alabama Taxable
    123462 Alabama Taxable
    123463 Alabama Taxable
    123464 Alabama Taxable
    123465 Alabama Taxable
    123466 Alabama Taxable
    123467 Alabama Taxable

    Sheet 2

    Avacode Alabama Alaska Arizona Arkansas California Colorado Connecticut Delaware District Of Columbia Florida Georgia Hawaii
    123467
    123466
    123465
    123464
    123463
    123462
    123461
    123460
    123459
    123458
    123457
    123456

  22. ITEM W ITEM X ITEM Y result
    a g 10 13
    b c apple 18
    c d 12 apple
    d a 13 12
    e b 18 cup
    f e cup 20
    g f 20 10

    i want to match as a=a=13, b=b=18, c=c=apple, d=d=12, e=e=cup, f=f=20, g=g=10.. can you suggest the formula?

    1. Sorry, I do not fully understand the task.
      As it's currently written, it's hard to tell exactly what you're asking.

  23. i have two sheet with items and its qty and items and its weight.. both sheet items are same but not in same order. i need to match these items from these two sheets and get the value of each items either qty or weight. so my final sheet shoud show ITEMS - WEIGHT - QTY..

  24. Hi I need to help with situation:
    I have row where I write Yes/no Let´s say R2:AE2 and to the D cell I neet to write something like: If all cells in row R2:AE2 are YES put there yes, if all are NO put NO if there is YES and NO leave it empty.
    Any ideas??
    Thank you!

    1. Hi! Use the COUNTIF function to count the number of cells with the text "Yes" or "No". Use the COLUMN function to count the number of cells in a string. Use the nested IF function for multiple conditions.
      The formula might look like this:

      =IF(COUNTIF(R2:V2,"Yes")=COLUMNS(R2:V2),"Yes", IF(COUNTIF(R2:V2,"No")=COLUMNS(R2:V2),"No",""))

      This should solve your task.

  25. Hi,
    I need a formula to check if one cell is greater than 3 other cells, then apply a specific text if this condition is met.

    For Example i have the Following Range: 25 50 75 100 (This range could change depending on the pre conditions)

    I want to write a formula that checks which is the highest value in the above range and returns a specific narration according to the highest value in the range.

    Thanks in advance.

  26. What if you are trying to determine if Column L lists one client multiple times? I want a Yes/No return in Column Z. Currently my formula is: =IF(OR(L7=$L$3:$L$1649),"Yes","No")

    the problem here is that L7 shouldn't be included in the range BUT I want all numbers before and after...I want the range to include L3:L6 AND L8:L1649.

    The problem is if I separate those two out into the following formula, it wont work as it comes up with a #SPILL error:

    =IF(OR(L7=$L$3:L6)&(L7=L8:$L$1649),"Yes","No")

    Any idea how to get each Row in Column Z to return an accurate Yes/No based on whether the name of the company in Column L is used in/the same as any other Row in Column L?

  27. I'm having a bit of a problem here :D
    What if I had four sets of colums A,B,C,D
    Column C consists of same letter+digit strings as column A, but columns C has them sorted not in the same order. Then column D consists of dates assigned to the strings in column C.
    How can I write formula that will check if column C has a cell with string of letters+numbers that matches a cell in column A - then if it's a match, column B will download the date from a corresponding cell (to cell in C column) in column D

    1. Hi! If I understand correctly, to find the value in column C and get the corresponding value from column D, you can use INDEX MATCH function. Here is an example formula:

      =INDEX(D1:D10,MATCH(A1,C1:C10,0))

      I hope it’ll be helpful.

  28. There are 3 columns: A, B, C

    If value of a cell of A match with any of the cell value of B column, I want to print value of C [match index of B] in result.

    Is it possible?

    Thanks in advance.

      1. Thanks you Alex for the reply, the INDEX MATCH seems to work for me, saved me lot of time.

  29. I have data in two sheets June and July with ID numbers and values in two different column. I wanna bring July yearly values in June sheet.
    Some ID's are similar number or duplicate in ID coloumn so cant use VLOOKUP or sumifs .

    Pls help

  30. i need help for this function: if column A has counting 1 to 5 and column B is empty and i put random number in column B. if entered number match with column A then then ok otherwise system move cursor where entered number exist

  31. How do match with formula in excel
    A1=P b1=D C1=as D1=cd
    A2=as b2=cd
    Result E1 CELL IN TRUE

    C1 AND D1 cell same value match in column A & B

  32. Alexander, Thank you for your help on this formula =IF(AND($G8<40000,OR($B8="AZ",$B8="NM")),"N/A","N"). I will clarify (it won't let me snippet a picture).

    Column B
    State Which is either AZ, CO, KS, NM, WY

    Column G-P&C
    Amounts ranging from -$168 to $500,000+

    Column H-Life
    Amounts ranging -$ numbers to $0 to blank to $100,000+

    Column J
    P&C Goal
    $40,000 for AZ, KS, NM-Formula to return Y,N
    N/A for CO, WY and Formula to return N/A
    Need this formula based on column B and G

    Column K
    Life Goal
    $4,000 for AZ, CO, KS, NM, WY
    Need formula based on column B and column H

  33. Also, if the column is blank I need it read as zero dollars.

  34. I have column B which indicates a state of AZ, CO, NM, WY. Column G indicates an amount in dollars. States of AZ and NM have a goal of $40,000 and CO and WY do not have the goal so I want to return N/A (not applicable). I am struggling with the formula. Nothing I am doing is returning a result =if(($g8<$40,000)AND($b8="AZ",$b8="NM"),"N/A","N")) or =IF(($B8="AZ")*OR($B8="NM")*AND($G8<40,0),"N/A","Y"). Yikes, I am in over my head!

  35. Hello! I am using the below formulate to find one cell's value in a column range but it is not working because the cells in that column range contain multiple values separated by spaces ;

    = IFERROR(INDEX($B:$B,MATCH(C1,$A:$A,0)),0)

    example - looking to match c1 value of 123 to a cell in column A and return the value next to the matching cell in column B - the problem is that the values in column A appear as such; "123 234 655"

    so the 123 is contained in cell A but because it has other values it returns 0

  36. Hi,

    I have multiple column where the value are mentioned. I need to identify the cell reference where the value of the cell is >=100.
    For example cell a3 = 0 , a4 = 70, a5 = 99,...a30 = 101

    Then through index match i will be able to identify the cell reference as A30.
    Plese help getting this figured out

    Regards
    Iyer

  37. IF(Z814>=F814,F814)+AND(Z814<=F814,Z814) Not working both the condition whih is only get the firt condidition kinldy guide this one

  38. Hello,

    Wondering if you're able to help me. If two cells match within the same column (cannot be case sensitive), is there a way to copy the data from some of the cells in the row of one match to the row of the other? Hoping you can help me. I have 40,000 columns containing duplicates! Thanks so much.

  39. Hello There I have a question here would help please?

    I use Office 365
    I need to make a formula that in case (for Example) A1= one cell of range (B2:B100) then C1 equal same cell number of range (D2:D100)

    I mean in case A1=B55 then C1=D55
    A90=B30 then C90=D30 --- but B column is a range of 100 cells and D is a range of 100 cells too
    like that - I'm collecting information from many tables so,

      1. Noooo it's not like that -- Look,
        I have 2000 items in A column ---> i have first specifications in B column -----> i have the second specification in C column (all specifications for all the items 2000 each on in same raw but different columns).

        Now in a new table i need to create dropdown list for the whole 2000 items to be once i choose any of those 2000 items in A1 -- first specification appears automatically in B1 and second specification appears automatically in C!1

  40. Hello! I have a question for you and hope you can help!
    I am setting up a sheet to help with pricing.
    I would like it set up so that I can enter a number of times that someone buys and in another cell it will calculate the total cost.
    But the more you buy the different price point it would be.
    so if buying 1 it's $2.50, buying 2 is $2.40...
    I tried using an IF formula =IF(A1=1, C3; A1=2; C4) but that's not correct.. not sure what I need to do differently!

    2. I figured it out! I used an IFS function instead -
      =IFS(E3="",0 ,E3=1, O10, E3=2,P10, E3>2,0)

  41. Hi Alexander, I am really stuck on a formula for the following;

    If i enter a value in C1, I want it to either permanently highlight or delete the matching value that is in C2:V21.
    When i change and add a new value in C1, I need the existing and new value to still be highlighted in C2:V21.

    Example:
    I enter '9' in C1.
    I need to permanently highlight or delete the '9' that is located in C2:V21.
    When i change C1 to '23', I need to then have both 9 & 23 permanently highlighted or deleted from C2:V21 and so on.

    Any assistance is appreciated.

    Thank you
    J

    PS. this is for a non-profit.

  42. I have 2 columns. Column A with 6 different items with dropdown menu (Apple, Bag, Pen, Pillow, Shoe, Flower) and the other column B with Status from dropdown menu (Lost, Found, Destroyed and Fixed). I want to create a formula in order to count for example how many apples are found , how many apples are lost, how many are fixed. Same for different items. What formula can I use?

      1. Brilliant! Thank you so much Alexander!
        One more question - I'm trying to find a formula to highlight the cell is the date is in 3 days so for example if today is 13th July all the cells for 14th, 15th and 16th July will highlight in red.

  43. I need to know if the value of one is A, B,C or D then take the total from collum E and put in collum F

    how is that done?

    1. Sorry, I do not fully understand the task.
      As it's currently written, it's hard to tell exactly what you're asking.

  44. I'm trying to work out this formula but haven't had any luck so far:

    Column A with 'Order Numbers'.
    Column B with 'Suppliers' (there's two of them).
    Column C to return those Order Numbers that appear next to / match both suppliers in Column B. And the ones that don't appear to both Supplers - show blank cell.

    1. Hi! If I understand your task correctly, try the following formula:

      =IF(COUNTIF($A$1:$A$100, A1)=2,A1,"")

      I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.

      1. Hi Alexander, thank you. I tried it, it returns blanks only.

        Here's a better example:

        Column A (order number) | Column B (product) | Column C (result)
        1 | 5001 | Apple | /blank/
        2 | 5002 | Apple | 2
        3 | 5002 | Apple | 3
        4 | 5002 | Orange | 4
        5 | 5002 | Orange | 5
        6 | 5003 | Orange | /blank/
        7 | 5004 | Apple | 7
        8 | 5004 | Apple | 8
        9 | 5004 | Orange | 9
        10 | 5004 | Orange | 10
        11 | 5005 | Apple | /blank/
        12 | 5005 | Orange | /blank/

        Column C I need to return those Line numbers where Order number appears next same product. In this examples this would be as shown above.

        1. Hi! I wrote this formula based on the description you provided in your original comment. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.

          =IF(COUNTIFS($A$1:$A$100, A1,$B$1:$B$100,B1)=2,ROW(),"")

  45. Hi,
    Is it possible to match two different columns of data and return values based on matching contents? I have two separate sheets containing an ID number and a corresponding date. Sheet A contains three variables, a customer ID number, a date and time whereas Sheet B contains only selection of these ID numbers and a corresponding date.

    I am hoping to match customers by their ID number and date of attendance from Sheet A to the matching values in Sheet B and if a match is detected to copy the time variable from Sheet A to the corresponding ID number and date in Sheet B, is this possible? I have tried combinations of IF, MATCH and INDEX functions but can't get to work as it's covering a range of customer ID's that may be in separate rows across the sheet. For Example ID Number 1 on 17/06/23 with a time of 12:00 could be in cell B1 in Sheet A but in B5 in Sheet B, can Excel check across a range of values for matches within another range?

    Thanks in advance.

  46. How to verify there’s only one Logic True Value in range of cells using combinations of OR and AND functions?

    1. The formula will return TRUE if there is only one TRUE logical value in the range.

      =SUM(--(A1:B10=TRUE))=1
      =COUNTIF(A1:B10,TRUE)=1

  47. Hi all,

    I am very stuck in a formula and would like some help. I have an export from a survey from Ms Forms, the answers to the survey have multiple options meaning that the excel report has multiple values in a cell divided with the symbol ";". We also have an option "other" which is free text choice. I need to build a calculator to automate the count of responses but i am not sure how to do that.

  48. Hello,

    I have 2 spreadsheets of nearly identical information between them, except 2 columns. I want to update the 2nd sheet when remaining values match between the 2 spreadsheets. I know I can order them similarly and then simply copy the column in question from 1 sheet to the 2nd, but I would rather have the accuracy of a formula.

  49. Hi,
    I was given this really difficult assignment and cannot seem to figure it out. On one sheet I have a matrix that consists of one column for lane numbers (ex. 1000) and two other columns for ranges of volume (ex. 0 to 50, 50 to 100). On another sheet I have the same lane number (1000) but different amounts of randomized volumes. How can I formulate so that it matches the same lane number from the matrix tab and counts how many times it fell in between the two ranges?
    I would greatly appreciate the help!!

  50. This helpful page explains ways to test if a cell matches any cell in a range, also to test if two ranges are equal. I'm trying to extend these methods to test if a range of three numbers in adjacent cells of column A matches a three-adjacent-number range found anywhere in column B which contains many such three-number ranges. For example, if column A contains (only) 8, 6, and 2 I want to search column B to see if the 8,6,2 sequence is present. Any suggestions would be appreciated. I'm using an older Excel version.

    1. Hello!
      Find the first match of A1 using the MATCH function. Use the OFFSET function to take two values below and compare them to A2 and A3. Use the AND function to combine the three conditions.

      =AND(ISNUMBER(MATCH(A1,$B$1:$B$100,0)),OFFSET(B1,MATCH(A1,$B$1:$B$100,0),0)=A2,OFFSET(B1,MATCH(A1,$B$1:$B$100,0)+1,0)=A3)

      If you have the TEXTJOIN function, you can combine the numbers into a text string and use the search as described in this manual: How to find substring in Excel

      =ISNUMBER(SEARCH(TEXTJOIN(",",TRUE,A1:A3),TEXTJOIN(",",TRUE,B1:B100)))

      1. Alexander,
        Thank you for your quick response and suggestion to use the OFFSET and MATCH functions to solve my problem. The specific code you provided does not quite solve the problem because it won't find the matching three-adjacent-number range if an earlier three-adjacent-number range contains the first digit of the search triplet. Using my prior example, if A1:A3 contain 8,6,2 and if B9:B11 contain 7,6,8 followed by 8,6,2 in B13:B15, the result should be TRUE because the B13:B15 numbers match. But the result is given as FALSE because the lower triplet isn't found. So far I have not found the right combination of OFFSET, MATCH, and AND functions to get around this limitation.

        1. Hi! I don't think you can solve this problem with an old version of Excel. Unfortunately, the functions you need are not available to you.

          1. OK, thanks for that info. I have a workaround that may be close enough for my purposes. Since I can't match arrays in my older Excel version, I will have Excel compute a single number for each three-adjacent-number item using a mathematical formula that combines all three numbers. Then a single-number MATCH function similar to what you suggested will let me find the cases I want. I can tolerate some small number of false positive matches which could result if the mathematical formula is too simplistic.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)