+ Reply to Thread
Results 1 to 5 of 5

Combine If with Index Match

  1. #1
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Combine If with Index Match

    I am trying to have my formula use the index match function if an adjacent cell contains the word "Inventory". If that same cell contains "Retired" then I need the formula to perform an index match with a table on another worksheet. Attached is my file that I am working on to further explain where I am at.

    To be clear, I need to write a formula that uses the index match function to check both worksheet tabs. For example, if INV00503 is type "Inventory", then it will return the corresponding value from the table on the Inventory tab. If it the type is "Retired" then it will return a value based on the tables in the retired tab.


    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Combine If with Index Match

    named ranges (per sheet):
    Inventory =INVENTORY!$B$3:$K$500
    Retired =RETIRED!$B$2:$K500

    Typical formula on (consolidated D4):
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Combine If with Index Match

    Thank you for your reply. I see your solution works. The only problem I see is the formula is dependent upon the number in which the column in the table resides, which if a new column is inserted in the table (which may happen) the formula will be broken. This is similar to if I used a VLookup formula, which also breaks is a new column is inserted.

    What I was hoping to do is incorporate two index/match formulas in one string that produced a result on the Consolidated tab depending upon the Type (Inventory or Retired) referenced in the adjacent column on the Consolidated tab. The vision i had was something like this: (Note: I am using cell D5 as an example as it contains some numbers in the Inventory tab)

    If B4="Inventory" then =IFERROR(INDEX(INVENTORY!$D$3:$D$552,MATCH($C5,INVENTORY!$C$3:$C$552,0)),""), Otherwise if B4="Retired" then =IFERROR(INDEX(RETIRED!$D$3:$D$552,MATCH($C5,RETIRED!$C$3:$C$552,0)),"")

    Of course I realize my above description is not a formula but hopefully clarifies the direction I was going.

    Thanks in advance.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Combine If with Index Match

    ... The only problem I see is the formula is dependent upon the number in which the column in the table resides...
    Please Login or Register  to view this content.
    Where
    MATCH(D$3,INDEX(INDIRECT($B4),1,0),0)
    will return the column of Inv/Ret which matches the column header in Cons. (providing that the corresponding headers are exactly the same)

    Note:
    named range:
    Inventory =INVENTORY!$B$3:$K$500

    should have been:
    Inventory =INVENTORY!$B$2:$K$500
    to include the header row.

  5. #5
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Combine If with Index Match

    Brilliant. This works perfectly. Thanks so much!

+ 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. Combine SUM, INDEX and MATCH
    By abrj in forum Excel General
    Replies: 7
    Last Post: 12-14-2018, 02:56 PM
  2. [SOLVED] Combine INDEX(MATCH with INDIRECT (maybe?)
    By sma365 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2017, 04:41 PM
  3. Combine index match and sumproduct
    By Esrei in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-25-2013, 08:08 AM
  4. [SOLVED] How to combine SUMIF, INDEX and MATCH
    By BeachRock in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-28-2013, 04:58 PM
  5. How Do I Combine OFFSET With INDEX MATCH
    By joconnor125 in forum Excel General
    Replies: 8
    Last Post: 06-15-2012, 04:47 AM
  6. [SOLVED] Combine MATCH and INDEX
    By SubwAy in forum Excel General
    Replies: 13
    Last Post: 05-31-2012, 11:53 AM
  7. How to combine the INDEX and MATCH functions?
    By Anna A in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-25-2010, 03:47 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