Add a helper column in the AV and CV sheets.. (you can hide these columns afterwards)

in F3 of each sheet enter formula:

=IF(A3="","",A3&"_"&B3)

copied down. You can go as far as you want so that future entries will be covered... or you can turn your data into tables so that the formula will copy down automatically as you enter new data.

In the Master sheet in B5 enter formula:

=IF($A5="","",IFERROR(IF(COUNTIF(CV!$F:$F,$A5&"_"& 0),INDEX(CV!C:C,MATCH($A5&"_"&0,CV!$F:$F,0)),INDEX (AR!C:C,MATCH($A5&"_"&0,AR!$F:$F,0))),0))

copied across to column D and down as far as you want to capture future data.


The enter same formula in E5 and change all the &0 in the formula to &1 to correspond to Rev number

e.g.

=IF($A5="","",IFERROR(IF(COUNTIF(CV!$F:$F,$A5&"_"& 1),INDEX(CV!C:C,MATCH($A5&"_"&1,CV!$F:$F,0)),INDEX (AR!C:C,MATCH($A5&"_"&1,AR!$F:$F,0))),0))

.... then copy to column G and down.

Repeat for each revision.