+ Reply to Thread
Results 1 to 3 of 3

Combination of IFs & Index/Match

  1. #1
    Forum Contributor
    Join Date
    10-09-2010
    Location
    London
    MS-Off Ver
    Excel Version Office 365
    Posts
    211

    Combination of IFs & Index/Match

    Dear Community

    I am struggling and would appreciate some advice please. It is probably a combination of IFs & Index/Match

    I have two tabs of data about 44,000 rows.

    In Initiatives 1, I currently have Outturn (£) run rate data

    In Initiatives 2, I have Weighted Pipeline (£) data

    I need to update initiative 1 Outturn (£) run rate column AN, with the value of Weighted Pipeline (£) - initiative 2 - column AN i.e. Replace

    It will look at the ID column E in both tabs and if initiative 2 has value in column AN it will replace that value in initiative 1 - column AN


    If there is no value is initiative 2, it will not replace but use the value that already exists in initiative 1
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Combination of IFs & Index/Match

    You can't have both a value AND a formula in a cell, so you can't have a formula in AN2 to decide what to display there - you will need to put the formula in another cell. You could put this formula in cell AO2 of Initiative 1:

    =IF(COUNTIF('Initiatives 2'!$E:$E,E3),IF(INDEX('Initiatives 2'!$AN:$AN,MATCH(E3,'Initiatives 2'!$E:$E,0))="",AN3,INDEX('Initiatives 2'!$AN:$AN,MATCH(E3,'Initiatives 2'!$E:$E,0))),AN3)

    then copy down.

    Hope this helps.

    Pete

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Combination of IFs & Index/Match

    Thanks for the rep - glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

+ 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. Combination of Index, Search, Match
    By charlesny09 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2019, 01:25 PM
  2. VBA index, match, lookup or some combination of all?
    By atdemeo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-06-2017, 06:24 AM
  3. [SOLVED] Help with index match combination
    By vdminh21 in forum Excel General
    Replies: 3
    Last Post: 06-08-2017, 04:58 AM
  4. [SOLVED] Index Match combination in VBA
    By Arnodekkers1995 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2017, 09:17 AM
  5. IF / INDEX-MATCH Combination
    By Joshua27 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-23-2015, 05:29 PM
  6. Combination of IF and INDEX/MATCH
    By flandophile in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-30-2012, 09:52 AM
  7. [SOLVED] Sum index and match combination
    By txbullets in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-08-2009, 06:21 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