+ Reply to Thread
Results 1 to 14 of 14

2D Array Lookup and Condition Check

  1. 12-05-2015, 04:29 PM #1
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    2D Array Lookup and Condition Check

    Hey guys, hope you can help me again. I have a 2D array with sets of x and y data repeating across many columns (i.e. B and C are corresponding values, D and E are corresponding, etc.). All the x values are unique, while the y values generally cannot be trusted to be unique. I need a formula to find a certain x value in the data, and see if it's y value is the same as the the one below it.

    The y value is always one cell to the right of the x value, and the one I want to compare to is always one cell below. If necessary, I can input the column/List where the certain x value is located. Attached is a spreadsheet illustrating this.

    I tried some index matches of index matches and fried my brain so hopefully someone can help. Thanks

    2D Array.xlsx

    EDIT: Ideally the result will work as part of an array formula
    Last edited by JYTS; 12-05-2015 at 04:32 PM.
    Reply With QuoteRegister To Reply
  2. 12-05-2015, 05:02 PM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,364

    Re: 2D Array Lookup and Condition Check

    Try


    =IF(INDEX(B1:I20,SUMPRODUCT(($B$1:$I$20=$C$24)*ROW($B$1:$I$20)),SUMPRODUCT(($B$1:$I$20=$C$24)*COLUMN($B$1:$I$20)))=INDEX($B$1:$I$20,SUMPRODUCT(($B$1:$I$20=$C$24)*ROW($B$1:$I$20))+1,SUMPRODUCT(($B$1:$I$20=$C$24)*COLUMN($B$1:$I$20))),"TRUE","FALSE")
    Reply With QuoteRegister To Reply
  3. 12-05-2015, 05:16 PM #3
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    Re: 2D Array Lookup and Condition Check

    That's it! Nailed it +1 thanks John.
    Reply With QuoteRegister To Reply
  4. 12-05-2015, 05:20 PM #4
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,169

    Re: 2D Array Lookup and Condition Check

    See the attached where I use two intermediate formulas to the right of the word you type in. Then it is just a mater of using Offset.

    Type your lookup word into cell C24 and see the result in C25.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  5. 12-05-2015, 05:24 PM #5
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    Re: 2D Array Lookup and Condition Check

    Thanks Marvin, interesting to see the different logic you guys have in solving this...
    Reply With QuoteRegister To Reply
  6. 12-06-2015, 12:59 AM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,407

    Re: 2D Array Lookup and Condition Check

    Withdrawn by FR
    Last edited by FlameRetired; 12-06-2015 at 01:08 AM. Reason: formula correction
    Dave
    Reply With QuoteRegister To Reply
  7. 12-06-2015, 01:02 AM #7
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,407

    Re: 2D Array Lookup and Condition Check

    Double posted.
    Last edited by FlameRetired; 12-06-2015 at 01:05 AM.
    Reply With QuoteRegister To Reply
  8. 12-06-2015, 01:02 AM #8
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,407

    Re: 2D Array Lookup and Condition Check

    Double posted.
    Reply With QuoteRegister To Reply
  9. 12-06-2015, 01:03 AM #9
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    Re: 2D Array Lookup and Condition Check

    Thanks FlameRetired, but that's not quite it. The lookup region is variable. "TV" will not work with that. Thanks though
    Reply With QuoteRegister To Reply
  10. 12-06-2015, 03:46 AM #10
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,407

    Re: 2D Array Lookup and Condition Check

    Yeah I noticed I missed that detail. Didn't withdraw in time.
    Reply With QuoteRegister To Reply
  11. 12-06-2015, 04:24 AM #11
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,407

    Re: 2D Array Lookup and Condition Check

    Array formula,
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 12-06-2015, 09:14 PM #12
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    Re: 2D Array Lookup and Condition Check

    That definitely works, thanks FlameRetired! Now I've got to apply a couple of these and see which is fastest with a large dataset......
    Reply With QuoteRegister To Reply
  13. 12-06-2015, 09:27 PM #13
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,169

    Re: 2D Array Lookup and Condition Check

    Can we make bets on which method will be fastest 😀
    Reply With QuoteRegister To Reply
  14. 12-06-2015, 09:29 PM #14
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    Re: 2D Array Lookup and Condition Check

    Hahaha, yeah the one with no array formulas you wrote! (but you used helper cells!)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Two Condition Array to Lookup Text in a Third Column
    By hurst2008 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-01-2014, 03:36 PM
  2. [SOLVED] Range to array, then check to see if the array contains a string
    By yeahyeahyeah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2012, 12:17 PM
  3. Check 2 IF Condition
    By zit1343 in forum Excel General
    Replies: 3
    Last Post: 04-20-2012, 12:46 PM
  4. Need to lookup value in an array while satisfying a condition
    By paulmli in forum Excel General
    Replies: 1
    Last Post: 10-14-2011, 05:30 PM
  5. multi condition format? lookup and date condition valid for.
    By D_Rennie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-13-2009, 11:37 AM
  6. Sumproduct - Condition based on lookup of a Lookup
    By Hari in forum Excel General
    Replies: 13
    Last Post: 05-31-2006, 04:30 AM
  7. VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY
    By Scott Lolmaugh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2006, 07:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1