I've done Array formula's before and got them working, but for whatever reason, it's not working on my current information.

Currently I've two sheets:

Master Sheet - 'Sheet1M'

2nd Sheet - 'Sheet2S'

Sheet1M contains the following:

<tbody>

</tbody>

Sheet2S contains the following:

<tbody>

</tbody>

So notice how the second sheet has instances where the number in Column A is duplicated because there's more than one ref in Column B

<tbody>

</tbody>

When I've done similar things previously this is the Array Formula I have used:

{=INDEX(Sheet2S!$A$2:$B$155508,SMALL(IF(Sheet2S!$A$2:$A$155508=Sheet1M!$A2,ROW(Sheet2S!$A$2:$A$155508)-1),COLUMNS(Sheet1M!$C2)),2)}

But for whatever reason it's simply not working now and I don't know why.

If anyone can help me where I'm going wrong or even seek a resolution via VBA code it would be very much appreciated!

Currently I've two sheets:

Master Sheet - 'Sheet1M'

2nd Sheet - 'Sheet2S'

Sheet1M contains the following:

<tbody> </tbody> | PART01 | |

<tbody> </tbody> | PART02 | |

<tbody> </tbody> | PART03 | |

<tbody> </tbody> | PART04 | |

<tbody> </tbody> | PART05 | |

<tbody> </tbody> | PART06 | |

<tbody> </tbody> | PART07 |

<tbody>

</tbody>

Sheet2S contains the following:

60521235 | REF1 |

60521236 | REF2 |

60521236 | REF3 |

542184 | REF4 |

542920 | REF5 |

542920 | REF6 |

60556032 | REF7 |

<tbody>

</tbody>

So notice how the second sheet has instances where the number in Column A is duplicated because there's more than one ref in Column B

60521235 | PART01 | REF1 | |||

60521236 | PART02 | REF2 | REF3 | ||

60521237 | PART03 | ||||

542184 | PART04 | REF4 | |||

542920 | PART05 | REF5 | REF6 | ||

<tbody> </tbody> | PART06 | ||||

60556032 | PART07 | REF7 |

<tbody>

</tbody>

When I've done similar things previously this is the Array Formula I have used:

{=INDEX(Sheet2S!$A$2:$B$155508,SMALL(IF(Sheet2S!$A$2:$A$155508=Sheet1M!$A2,ROW(Sheet2S!$A$2:$A$155508)-1),COLUMNS(Sheet1M!$C2)),2)}

But for whatever reason it's simply not working now and I don't know why.

If anyone can help me where I'm going wrong or even seek a resolution via VBA code it would be very much appreciated!

Last edited: