Find and Match values in a range with values in another range and show/illustrate their position
I’ve tried using different formulas but I can’t quite make it work. So I attached a clean file here.
I have several rows (range C:Q) which values need to be found, matched, and illustrated with 9 boxes (tables), but one row to one box at a time in the following manner:
1. If I type an "X" in one of the cells in column S, then that row's values must be matched with the values of the box (table) which also has been marked with an "X"
a. For example: the values in the row range C4:Q4 need to be found and matched with the values in box (table) 5, the one with the X in the yellow cell (see attached file).
b. NOTE: only one cell (row) at a time will have the X in column S, and also only one box (table) at a time will be marked with and X (the yellow cell).
2. Then, if found and matched, write the matching values in the orange table in their corresponding positions.
3. Finally, the formulas should automatically adjust accordingly when I write an X in a different row in column S AND also adjust if I place an X in a different box (table).
p.s. I am using Excel 2010.
Bookmarks