# Combination of IFs & Index/Match

1. ## 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

2. ## 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. ## Re: Combination of IFs & Index/Match

Thanks for the rep - glad to help.

Pete

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

#### 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