Lookup to search tabs

[JasAujla]

Hi All,

I have a Change Request register spreadsheet and require a formula to pull data from other sheets into the main register based on Business Change Request number (BCR43)

Example

The formula is pulling data from a tab named BCR43, is there a better formula to pull data from other tabs based on BCR ref in column A?

 

[earthworm]

If the data is in same sheet you can use vlookup . Based on attached sample i don't see any other sheet .

However If there are other sheets I think the formula currently is best and dynamic.