I am trying to return a column based on column headers. The data displayed is dynamic, based on a dropdown selection and the header names may thus be in different columns.

My data headers are in cells W6:AO6. Here is how it looks:

	W	X	Y	Z
6	Ticket number	Additional Details	Production Started	Production Completed
7	14392841	N/A	11/18/2024 07:59:17	11/18/2024 11:27:41
8	14339754	N/A	11/18/2024 09:59:24	11/18/2024 12:22:04
9	14327042	N/A	11/18/2024 10:53:19	11/18/2024 13:27:17
10	14275929	N/A	11/18/2024 07:40:41	11/18/2024 11:00:18
11	14318523	N/A	11/19/2024 06:15:34	11/19/2024 08:33:36

I am trying to return a column based on column headers. The data displayed is dynamic, based on a dropdown selection and the header names may thus be in different columns.

My data headers are in cells W6:AO6. Here is how it looks:

	W	X	Y	Z
6	Ticket number	Additional Details	Production Started	Production Completed
7	14392841	N/A	11/18/2024 07:59:17	11/18/2024 11:27:41
8	14339754	N/A	11/18/2024 09:59:24	11/18/2024 12:22:04
9	14327042	N/A	11/18/2024 10:53:19	11/18/2024 13:27:17
10	14275929	N/A	11/18/2024 07:40:41	11/18/2024 11:00:18
11	14318523	N/A	11/19/2024 06:15:34	11/19/2024 08:33:36

I have tried this formula in V7:

=QUERY(W7:AO,"SELECT "&SUBSTITUTE(ADDRESS(1, MATCH("Production Completed",W6:AO6,0), 4),1,""))

I get a #VALUE error saying:

Unable to parse query string for function query parameter 2: NO_COLUMN: D

In the sample, the expected result would be Z7:Z.

This formula works if my data starts in Column A, but because the MATCH part of the formula returns 4 which is Column D, I get an error. In addition to various other attempts, I tried using OFFSET, but I could not get the formula to work.

Here is a sample sheet where the data or spreadsheet setup itself doesn't make much sense.

I have removed the actual data for privacy reasons: https://docs.google/spreadsheets/d/1soFQnGsuIJLc0WuUtrSUgvR9JUgAN5Z8TvRoOBnw2r0/edit?gid=247110533#gid=247110533

Any suggestions on how to tweak this formula to get it to work? Or is there a better formula to use? Thanks for your help.

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited Nov 20, 2024 at 15:57 Codedabbler asked Nov 20, 2024 at 11:46 CodedabblerCodedabbler 1,08211 gold badge44 silver badges1717 bronze badges 1

• Can you provide a clear and concise expected result to help the community easily understand the problem statement – Patsytalk Commented Nov 20, 2024 at 12:03

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

You may try:

=choosecols(W7:AO,xmatch("Production Completed",W6:AO6))

OR

=query(W7:AO,"select Col"&xmatch(Z6,W6:AO6))

Try this Alternative approach

Using INDEX() Returns the content of a cell, specified by row and column offset.

Formula used

=INDEX(W7:AO, , MATCH("Production Completed", W6:AO6, 0))

Sample output

Reference

• INDEX