Use Record Number and Heading Names to sort rows to columns, skipping blank cells

Question

Sample data here: https://docs.google.com/spreadsheets/d/1q4qRgXSq6xXhddArPCoBIuOOupRvokUd0andADCNN0Q/edit

I have sample data that looks like this:

I want the result to look like this:

1. I need one formula that uses the RED number (e.g "J1") to pull data from that record number on the table (not row number)

2. I need to pull the information by Heading name (not Column Letter);

3. I need to skip blank cells.

I have tried a number of things, but I'm just not good enough at Google Sheets coding. But I did get this far, pulling information by heading name:

=INDEX('Sheet1'!$A$1:$AW,MATCH(J1,'Sheet 1'!$A$1:$A,false),MATCH("FIRST",'Sheet 1'!$A$1:$AW$1,false))

Yet I can't figure out how to stack this for multiple cells and then to skip cells that are blank.

Answer 1

You may try:

=tocol(filter(choosecols($B5:$G,xmatch({"FIRST","SPOUSE","CELL","EMAIL"},$B4:$G4)),$A5:$A=J1),1)