INDEX MATCH from 2 worksheets onto third worksheet

Hello,

So I'm using the following formula in 2 separate worksheets (sheet 1 & sheet 2) to count the yes'.

Please Login or Register  to view this content.

I then have a third worksheet that I'd like to pull all the yes' from both the worksheets and compile into 1 complete list. I'm using the following formula which works fine if I'm pulling info from 1 worksheet.

Please Login or Register  to view this content.

I cant seem to figure out how to index and match both worksheets. I've tried this..

Please Login or Register  to view this content.

But it doesn't work.

Any help would be greatly appreciated

I would change the first formula to this in I2 of both sheets:

=IF(H2="Yes", MAX($I$1:$I1)+1,"")

When you copy this down you just get consecutive sequentially-numbered records for those where column H is yes. In I1 of the second sheet (New Prospects) you need this formula:

=MAX('Lapsed Customers'!$I:$I)

so that the numbers follow on in the second sheet. Then in your 3rd sheet you can have this formula in column A:

=IF(ROWS($1:1)>MAX('New Prospects'!$I:$I),"",IF(ROWS($1:1)<'New Prospects'!$I$1,INDEX('Lapsed Customers'!A:A,MATCH(ROWS($1:1),'Lapsed Customers'!$I:$I,0)),INDEX('New Prospects'!A:A,MATCH(ROWS($1:1),'New Prospects'!$I:$I,0))))

and then copy across and down as required.

Hope this helps.

Pete

Originally Posted by Pete_UK

I would change the first formula to this in I2 of both sheets:

=IF(H2="Yes", MAX($I$1:$I1)+1,"")

When you copy this down you just get consecutive sequentially-numbered records for those where column H is yes. In I1 of the second sheet (New Prospects) you need this formula:

=MAX('Lapsed Customers'!$I:$I)

so that the numbers follow on in the second sheet. Then in your 3rd sheet you can have this formula in column A:

=IF(ROWS($1:1)>MAX('New Prospects'!$I:$I),"",IF(ROWS($1:1)<'New Prospects'!$I$1,INDEX('Lapsed Customers'!A:A,MATCH(ROWS($1:1),'Lapsed Customers'!$I:$I,0)),INDEX('New Prospects'!A:A,MATCH(ROWS($1:1),'New Prospects'!$I:$I,0))))

and then copy across and down as required.

Hope this helps.

Pete

Hi Pete,

Thanks, that seems to be working.

However, It's matching row 1 from the New Prospects sheet which is the title for each column. How do I rectify that?

Originally Posted by Pete_UK

I would change the first formula to this in I2 of both sheets:

=IF(H2="Yes", MAX($I$1:$I1)+1,"")

When you copy this down you just get consecutive sequentially-numbered records for those where column H is yes. In I1 of the second sheet (New Prospects) you need this formula:

=MAX('Lapsed Customers'!$I:$I)

so that the numbers follow on in the second sheet. Then in your 3rd sheet you can have this formula in column A:

=IF(ROWS($1:1)>MAX('New Prospects'!$I:$I),"",IF(ROWS($1:1)<'New Prospects'!$I$1,INDEX('Lapsed Customers'!A:A,MATCH(ROWS($1:1),'Lapsed Customers'!$I:$I,0)),INDEX('New Prospects'!A:A,MATCH(ROWS($1:1),'New Prospects'!$I:$I,0))))

and then copy across and down as required.

Hope this helps.

Pete

Hi Pete,

Thanks, that seems to be working.

However, It's matching row 1 from the New Prospects sheet which is the title for each column. How do I rectify that?

Just change the less than symbol to less than or equal, as shown in red below:

=IF(ROWS($1:1)>MAX('New Prospects'!$I:$I),"",IF(ROWS($1:1)<='New Prospects'!$I$1,INDEX('Lapsed Customers'!A:A,MATCH(ROWS($1:1),'Lapsed Customers'!$I:$I,0)),INDEX('New Prospects'!A:A,MATCH(ROWS($1:1),'New Prospects'!$I:$I,0))))

then copy across and down.

Hope this helps.

Pete