Given multiple columns, insert a value at a particular position in Excel

Solution 1:

If you have Office 365, and if your desired output is incorrect given your input, and the column you labelled House ID in your output is really the House, then you can use the following formula:

H2: =IFERROR(FILTER(Pref[[Preferences]:[Preferences]],(Pref[[House]:[House]]=$G2)*(Pref[[Preferences Rank]:[Preferences Rank]]=COLUMNS($A:A))),0)

then fill down and across to fill the table

If you have an earlier version, you can use:

=IFERROR(INDEX(Pref[[Preferences]:[Preferences]],AGGREGATE(14,6,1/((Pref[[House]:[House]]=$G2)*(Pref[[Preferences Rank]:[Preferences Rank]]=COLUMNS($A:A)))*ROW(Pref),1)-ROW(Pref[#Headers])),0)

Note that I named your "original problem" table Pref and I am using structured references, but you can change this to regular addressing if you prefer.

Input

Output