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

[mmcoon01]

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"

NOTES
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]

Hi,

Please try to use the formula as the below syntax:

=INDEX(BESTRFS[BEST_RFS], MATCH(MasterCap cell B2, BESTRFS[ACAP],0))

Where:

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

[#3]

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.