+ Reply to Thread
Results 1 to 8 of 8

IF and INDEX/MATCH combo with relative/dependent cell value

  1. #1
    Registered User
    Join Date
    09-13-2016
    Location
    Warsaw, Poland
    MS-Off Ver
    2019
    Posts
    32

    Question IF and INDEX/MATCH combo with relative/dependent cell value

    I am trying to create a recipe book in Excel 2013, that has a "main page" where you choose what recipe you want, and it shows you the recipe and ingredients.
    I created two dependent drop down lists: in the first one you choose from Breakfast, Brunch, Lunch, Dinner, Supper etc., and once you choose from it, the other drop down list allows you to choose from recipes filed under the main category, I used the INDIRECT function with data validation to achieve that and it works perfectly.

    The problem appears when I try to write a relative formula that shows ingredients depending on what you chose from the second drop down list (cell C3). I have the ingredients written down on a separate sheet: (sheet Ingredients). I have the names of recipes in column A, and the ingredients in column B. I can't figure out how to write the formula without having to manually elongate it with each recipe I add. What I got now is:

    =IF(C3="Pancakes"; INDEX(Ingredients!B:B; MATCH("Pancakes"; Ingredients!A:A;0)); IF(C3="Banana Oatmeal"; INDEX(Ingredients!B:B; MATCH("Banana Oatmeal"; Ingredients!A:A;0)); IF(C3="Millet Porridge"; INDEX(Ingredients!B:B; MATCH("Millet Porridge"; Ingredients!A:A; 0)); "Not Found")))

    My question is: is there a way to write it like =IF(C3="any x"; INDEX(Ingredients!B:B; MATCH("any x"; Ingredients!A:A;0)); "Not Found") and end it at that.

    I will much appreciate all ideas!
    Last edited by Nina579; 09-19-2016 at 06:23 AM. Reason: status change to solved

  2. #2
    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,195

    Re: IF and INDEX/MATCH combo with relative/dependent cell value

    Perhaps ...

    =IFERROR(INDEX(Ingredients!B:B; MATCH(C3; Ingredients!A:A;0)); "Not Found")
    Last edited by JohnTopley; 09-13-2016 at 06:17 AM.

  3. #3
    Registered User
    Join Date
    09-13-2016
    Location
    Warsaw, Poland
    MS-Off Ver
    2019
    Posts
    32

    Re: IF and INDEX/MATCH combo with relative/dependent cell value

    Quote Originally Posted by JohnTopley View Post
    Perhaps ...

    =IFERROR(INDEX(Ingredients!B:B; MATCH(C3; Ingredients!A:A;0)); "Not Found")
    Thanks! This works great. I had no idea you can use IFERROR like this.... (still an excel beginner tbh)

  4. #4
    Registered User
    Join Date
    09-13-2016
    Location
    Warsaw, Poland
    MS-Off Ver
    2019
    Posts
    32

    Re: IF and INDEX/MATCH combo with relative/dependent cell value

    By the way, I looked at this file with a fresh eye today, and realized the simplest possible solution, which is VLOOKUP. I tried to use INDEX/MATCH yesterday for whatever reason, but with VLOOKUP it just is =VLOOKUP(C3;Ingredients!A:B;2;0) and it does the exact same thing. Thanks for the help though! (Even if I'm not exactly sure how IFERROR works in this case xD).

  5. #5
    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,195

    Re: IF and INDEX/MATCH combo with relative/dependent cell value

    VLOOKUP and INDEX/Match are (often) interchangeable.


    =IFERROR(VLOOKUP(C3;Ingredients!A:B;2;0),"Not Found")

  6. #6
    Registered User
    Join Date
    09-13-2016
    Location
    Warsaw, Poland
    MS-Off Ver
    2019
    Posts
    32

    Re: IF and INDEX/MATCH combo with relative/dependent cell value

    Yeah, but VLOOKUP works in my case on its own without the IFERROR formula, and INDEX/MATCH doesn't seem to.

  7. #7
    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,195

    Re: IF and INDEX/MATCH combo with relative/dependent cell value

    If there is no match VLOOKUP will return #N/A so using IFERROR is sensible,

  8. #8
    Registered User
    Join Date
    09-13-2016
    Location
    Warsaw, Poland
    MS-Off Ver
    2019
    Posts
    32

    Re: IF and INDEX/MATCH combo with relative/dependent cell value

    True that. Ok now I finally get everything. Thank you so much for your help.

+ 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. [SOLVED] Index/Match dependent with range dependent on another column
    By golden2282 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2016, 01:12 AM
  2. Replies: 3
    Last Post: 09-24-2015, 08:20 AM
  3. [Help] Dependent DDL with Index Match function
    By pappu6600 in forum Excel General
    Replies: 10
    Last Post: 09-24-2014, 08:29 PM
  4. [SOLVED] Dependent List With Index Match
    By NOTIMPORTANT in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-08-2014, 03:15 AM
  5. Replies: 2
    Last Post: 01-14-2014, 08:31 PM
  6. [SOLVED] Help with dependent lists INDEX MATCH
    By jet2004uk in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-19-2012, 12:00 PM
  7. IF dependent INDEX MATCH
    By jwaldmann in forum Excel General
    Replies: 2
    Last Post: 02-11-2012, 10:32 AM

Tags for this Thread

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