Vlookup with column search by headers

Question

I am downloading the "Data" report from the system, in which the order of the columns may be different, but their headers will always be the same and the first "ID" column will always be the first. I need values to be displayed in the "Report", where from the "data" sheet it will search for a value where the row will always indicate the first column with "ID", and then in the entire sheet "data" it will find a column with the same header as in the "report "and matches the value. Something like a vlookup, only without specifying column indexes. Tried on with query, but can't figure it out.

=QUERY(Data, "select * where A = """&$A3&""" and F Matches """&F1&"""",0)

Example: https://docs.google.com/spreadsheets/d/16yPVdOEgCraTHv9R2Qzmxme7hJpzwNRxcW12F7LhXs8/edit?usp=sharing

Answer 1

Delete everything in your Report sheet from B2 down and to the right (i.e., all you should have left is data in Col A and Row 1).

Place the following formula in cell B2:

=ArrayFormula(IF(A2:A="",,IFERROR(VLOOKUP(A2:A,Data!A:H,HLOOKUP(FILTER(B1:1,B1:1<>""),{Data!1:1;COLUMN(Data!1:1)},2,FALSE),FALSE),"???")))

This will work like a regular VLOOKUP to find the ID and then employ HLOOKUP ('Data' headers over their column numbers) to return the VLOOKUP column requests.

If no match is found, IFERROR will return the friendlier "???" instead of an error.

The formula is written to be as flexible as possible. The only thing you'll want to change is making Data!A:H reflect your full Data-sheet range (e.g., 'Data!A:BB', etc.), which is something I could not guess. With that in place, the rest will adapt as you add more data in Col A or across the Row 1 header range.

Answer 2

It is certainly not the most elegant but I have solved the problem with this formula:

=QUERY(Data!A2:H4,"select "&choose(MATCH(F1,arrayformula(Data!A1:H1) ,0),"A","B","C","D","E","F","G","H","I")&" where A = """&A3&"""",0)

with result = "Scrum Master"