+ Reply to Thread
Results 1 to 6 of 6

IF Statement with Index Match

  1. #1
    Forum Contributor
    Join Date
    09-21-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    449

    IF Statement with Index Match

    Hello All,

    Please see attached and below formula.

    =IFERROR(INDEX(REPORT!$P:$P,MATCH(B2&"PO700AAS"&F2,REPORT!$B:$B&REPORT!$I:$I&REPORT!$H:$H,0)),"")

    Here is what the formula is doing.
    • Indexing column P on the Report tab
    • Matches the ID column in both reports
    • Matches the string "PO700AAS" with column I on the report tab
    • Matches the Comp column on both tab

    Here is what I need it to do
    • Instead of matching the string "PO700AAS" with column I on the Report tab, it needs to do an If Statement
    • The IF formula needs to look at the job in column D of the Tracker Tab and use the following logic
    • If the job in column D of the tracker tab is 700, the formula should look for "PO700AAS" in column I of the report tab
    • If the job in column D of the tracker tab is 1900, the formula should look for "PO1900AAS" in column I of the report tab
    • If the job in column D of the tracker tab is 2100, the formula should look for "PO2100LAB" in column I of the report tab

    Let me know if this does not make sense.
    Attached Files Attached Files

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: IF Statement with Index Match

    Give this a try

    Array formula, Use Ctrl+Shift Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Contributor
    Join Date
    09-21-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    449

    Re: IF Statement with Index Match

    Quote Originally Posted by mike7952 View Post
    Give this a try

    Array formula, Use Ctrl+Shift Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This almost works. However, my Auts don't share the same similarities in my real world example. I have made a change. See attached.

    I changed one of my Auths so that using the "PO"&D2""*" part of the formula now doesn't work. I also tried tossing in a formula to account for the change in one of the auths. Fail.
    Attached Files Attached Files

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: IF Statement with Index Match

    Try changing "PO" with "*"

  5. #5
    Forum Contributor
    Join Date
    09-21-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    449
    Quote Originally Posted by mike7952 View Post
    Try changing "PO" with "*"
    Replace that in your formula?

    My real life example has many different Auths that dont appear in my example. This match should be spot on so it doesn't return data that doesn't correspond.
    Last edited by morerockin; 07-17-2017 at 12:05 PM.

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: IF Statement with Index Match

    So in your first post those 3 IF's are not the only 3? If your real life example has many more, how many more? I don't remember how many nested ifs you can have in a formula.

+ 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] Using an IF statement with INDEX / MATCH
    By Fermented Reptile in forum Excel General
    Replies: 3
    Last Post: 02-13-2017, 08:47 AM
  2. index match with if statement
    By Per_s in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-30-2016, 08:48 AM
  3. If statement with INDEX MATCH
    By JPSIMMON in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-13-2015, 01:19 PM
  4. [SOLVED] INDEX MATCH with If statement
    By geliedee in forum Excel General
    Replies: 1
    Last Post: 04-14-2015, 07:49 AM
  5. [SOLVED] If Statement (Match/Index)
    By usmanfarok in forum Excel General
    Replies: 12
    Last Post: 09-26-2012, 04:42 AM
  6. Index & Match with IF statement
    By reggie1000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-20-2007, 01:49 PM
  7. Index() & Match() or just an if() Statement?
    By JustMe602 in forum Excel General
    Replies: 6
    Last Post: 12-12-2006, 04:24 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