+ Reply to Thread
Results 1 to 6 of 6

Index and Match Only If Adjacent Cell Value is True

  1. #1
    Registered User
    Join Date
    09-16-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Index and Match Only If Adjacent Cell Value is True

    Hi Guys,

    I would be very grateful if someone could help me.

    I have a spread sheet with the following columns:

    A: Equipment Number
    B: PM Number
    C: PM Ref

    There are duplicates of some Equipment Number values in column A, as some Equipment Numbers have more than one PM Number and either a PM Ref of 1 or 2.

    In a separate table, I am trying to insert a formula into column F that returns the ‘PM Number’ for each ‘Equipment Number’ value with a ‘PM Ref’ of 1.

    I have tried various INDEX and MATCH combinations, but I am struggling to match the Equipment Number and PM Number with the ‘PM Ref No’ of 1.

    I have attached an example.

    Any help would be greatly appreciated.

    Many thanks,

    Duggerz
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Index and Match Only If Adjacent Cell Value is True

    F2: =LOOKUP(2,1/(A$2:A$11=E2)/(C$2:C$11=1),B$2:B$11)

    copy down

  3. #3
    Registered User
    Join Date
    09-16-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Index and Match Only If Adjacent Cell Value is True

    Excellent!!

    Thank you very much for such a fast response. Really appreciate it.

    There are some very clever people on here and its great that they take their time to help others.

    Thanks again!

    Duggerz

  4. #4
    Registered User
    Join Date
    09-16-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Index and Match Only If Adjacent Cell Value is True

    Test2.xlsxHi Teethless Mama,

    Sorry to trouble you again.

    Your solution for the example I provided worked a treat, but when I applied it to my spreadsheet, it didn't work (my fault as I should have explained the problem better).

    In my spreadsheet, there could be any number of values in Column C (PM Ref), not just 1's and 2's and the search value may not always be 1 (I did that to try and provide a simple example). I see that the formula relies on all Column C values being either 1 or 2 in order to return the desired result. Do you know how we might be able to get the formula to work regardless of the other values in Column C (PM Ref)?

    I have attached a revised test spreadsheet. Please note that the actual spreadsheet I am working with has many more 'PM Ref' values than 1,2,3 and 4.

    Any help would be greatly appreciated.

    Duggerz

  5. #5
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Index and Match Only If Adjacent Cell Value is True

    Hi duggerz..

    did you adjust the reference according to your data range..

    =LOOKUP(2,1/(A$2:A$21=E2)/(C$2:C$21=1),B$2:B$21)
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  6. #6
    Registered User
    Join Date
    09-16-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Index and Match Only If Adjacent Cell Value is True

    Thanks Debraj,

    it was a long day - it worked!

    Thanks for your help!

    Duggerz

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 04-12-2013, 05:50 AM
  2. [SOLVED] Match text in 2 columns, if true output adjacent cell to the match.
    By kenoboy in forum Excel General
    Replies: 3
    Last Post: 07-03-2012, 09:52 AM
  3. Index/Match and return adjacent cell or workaround
    By alltimetop100 in forum Excel General
    Replies: 2
    Last Post: 03-02-2011, 08:24 AM
  4. match value in one column to adjacent value: use vlookup or index/match?
    By conorsgaffney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-28-2010, 04:59 PM
  5. Replies: 2
    Last Post: 03-16-2009, 01:09 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1