Index and Match: Copy Values based on Matching Cells

I need to fill in blank cells with valued cells based on matching cells in two different worksheets.


BESTRFS Table (Link)



MASTERCap Table (Link) 



The cells in MasterCAP[RFS_ID] must be filled in with the value in BESTRFS[BEST_RFS] when MasterCAP[CAP} matches BESTRFS[ACAP] match. For example, MasterCap A2 would fill in with “134” because the CAP fields both match to “H.3”


Using Excel 2016.

There are more columns and rows than shown in the example.

Columns and Rows will be added, deleted, and rearranged during work. So the CAP column may not always be column B.

Rows are sorted quite often.

I have attempted Index / Match but am making quite a mess of it. (I keep getting value errors.)

Many thanks.


By: Michele Coon

Leave a Reply

Your email address will not be published. Required fields are marked *