INDEX MATCH - Fill in a blank cell based on two matching cells

New Contributor

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



The cells in MasterCAP[RFS_ID] must be filled in with the value from BESTRFS[BEST_RFS] when MasterCAP[CAP] cell matches a BESTRFS[ACAP] cell. For example, MasterCap cell 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.

2 Replies



Please try to use the formula as the below syntax:



BESTRFS[BEST_RFS] is the column of values you want to retrieve.

MasterCap cell B2 is the cell in MasterCap table that you want to match.

BESTRFS[ACAP] is the column that you want to match cell B2 in it.


There is an example in the attached workbook.


Hope that helps

Dear Haytham -


I replaced cell B2 with the column name (which was CAP in my case) and it worked like a charm. Many thanks for your help. Wishing you only good things.


Kindest Regards,


Mickey C.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies