How do I fill in data using index, match, etc. in MS Excel?

Question

I have data formatted like so.

 1, 2, 3, 4, null, null, null

 A, B, C, D, null, null, null

 1, null, null, 4, 5, 6, 7

 A, null, null, D, 8, 9, 10

I want to copy the data from row three into the missing "null" cells in row one, because there is a match on cell values one and four upon comparing row one and row three.

So far I have the following.

 =INDEX(A2:AE346, MATCH(A351&F351&G351, A2:A346&F2:F346&G2:G346, 0), 2)

Here is a spreadsheet with some sample data so you can test your formulas.

Answer 1

Referring to incorporated simple sample data (the 4 rows by the 7 columns), actually, you have two tables sorted by column B then by Column A, a target table and an original table.

Make sure you sort the data as described above. Insert a new column in column A this formula:

A1: =B1&E1

and copy it down for both (in mind) tables.

Separate the table by inserting a new empty line (this is not necessary, but you want to do this).

After sorting as required above and inserting the separating row, the target table will start from line 1 and the second table will start from line 4.

The null data for the target table will start at F1. Type there this formula:

F1: =VLOOKUP($A1,$A$4:$H$5,COLUMN(F:F),0)

Copy the formula down and right over the null data of the target table.

That's it, you are done!

If you can't afford sorting, add a column with row numbering for future sorting back to the original order.

If you are not allowed to insert the column A, then use the first new empty column after the data, and use the same formula written for A1 above. Then instead of the VLookup, use Index/Match (considering you have row numbering column in H) The key formula is:

I1: =A1&D1

for the data (replacing the nulls):

E1: =INDEX($E$4:$G$5,MATCH($I1,$I$4:$I$5,0),COLUMN(A:A))

copy it down and right.

and here you've done once more.

If you have to sort the data back, copy and paste values first.

If there are rows in the target table which do not have a matching row in the original table, and you wish to get rid of the error messages, use this:

E1: =IFERROR( the formula in use, "")

for the Index/Match solution:

E1: =IFERROR(INDEX($E$4:$G$5,MATCH($I1,$I$4:$I$5,0),COLUMN(A:A)),"")

Good luck.