0

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

2 Answers 2

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.

2
  • Nice and complete !
    – Daniele
    Apr 7 at 19:37
  • Thanks, Daniele.
    – Erik Tyler
    Apr 8 at 0:38
0

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.