Populating Excel table using 2 cell coordinates - lots of data

Question

I need your help with this problem I'm facing using Excel. Basically I need to populate a table, or better, a given group of cells (dimensions 3x3), and my starting point is:

row numb. column name. value
2           A           10
3           C           4
4           B           20

so I would need to obtain a group of cell like this:

1    A      B    C
2    10     0    0
3    0      0    4
4    0      20   0

where the cells that have no value can be 0 or whatever. In other words, I have the coordinates where I need to insert the value, and I need to be insert the value corresponding to the coordinates in the right cell.

I already tried many times using functions like MATCH, VLOOKUP and INDEX (also ROW and COLUMN) but without success, because it inserts the correct values but also other combinations of coordinates that I don't want.

My idea was to start from cell A2 and, with the formula, check if there's a match with the coordinates of row and column in the data, and finally print the value.

PS: I would prefer to avoid VBA.

Answer 1

Try this array formula: (Of course, change your cell ranges as necessary)

= IFERROR(INDEX($G$2:$G$4,MATCH(TRUE,ADDRESS(ROW(),COLUMN(),4)=($F$2:$F$4&$E$2:$E$4),0)),0)

Note this is an array formula, so you must press Ctrl+Shift+Enter rather than just Enter after typing the formula.

See working example below.