Get column by finding value in the row

Question

In Excel, I've looked into hlookup, vlookup, match and index but none of these functions do following:

I need to find a text value in A1:Z1 and get the column of it. For example, I found the value in F1, then I want a result F:F.

EDIT: This is the function I want it to be added to:

=COUNTIFS(Source!B:B;Result!C3;Source!AT:AT;Result!$D$2)

I need the Source!B:B and alternatively Source!AT:AT be a search function that looks for a specific value in my table row and column in different sheet (Source).

Answer 1

INDEX function can return a whole column, e.g. assuming you want to search for "x" in A1:Z1 try

=INDEX(A:Z;0;MATCH("x";A1:Z1;0))

Note: this doesn't return a text string like F:F, it returns a reference to the column in question which you would normally use within a function that expects a whole column reference, e.g. SUM, SUMIF, LOOKUP etc.

Edited: You can use the above in COUNTIFS function like this:

=COUNTIFS(INDEX(Source!A:Z;0;MATCH("x";Source!A1:Z1;0));Result!C3;Source!AT:AT;Result!$D$2)

That makes the first range dynamic (between columns A and Z - extend as required) based on where "x" is first found in Source!A1:Z1, e.g. if "x" is first found in J1 then COUNTIFS uses Source!J:J for the first range - you can do that for any of the ranges in COUNTIFS

Answer 2

You can use this (it will return E:E, which you could then use INDIRECT with to return a workable range). This uses "words" as the word to match and stop at the first match:

=SUBSTITUTE(
    ADDRESS(1,MATCH("words",$A$1:$Z$1,0),4),"1",
    ":"&
    SUBSTITUTE(ADDRESS(1,MATCH("words",$A$1:$Z$1,0),4),"1",""))

This works by using MATCH to find the position of the occurrence in your range (in this case, 5), and then using ADDRESS, with row_num = 1 and column_num = the result of the match. Using 4 for the abs_num argument ensures that the returned value will be without the $'s. You then substitute out the 1 in E1 with ":" and concatenate it with the same formula, giving you E:E as a string. You can then do look-ups based on that range (using INDIRECT), such as this: