CODEWITHSUNDEEP
excelexcel-formulaexcel-2016
testing
testing

Reputation: 20289

Search multiple columns and return first row of column (header) if found

My goal is to get the header (first row in another sheet) if the value is found in the column.

Because VLOOKUP and HLOOKUP have some issues (e.g. search criteria has to be in the first column of the matrix), I have to find some alternatives. For VLOOKUP I'm using INDEX together with MATCH. Now I'm searching for an alternative for HLOOKUP. I found this post, but can't get it working.

I have two sheets:

column

Car        Living        Boat
----------------------------------
Kia        appartment    Jet-Ski
Chevrolet  house         speedboat
Ford       condominium   Yacht
Audi

search column

Original formula:

=INDEX(column!A1:C1;;MIN(WENN(column!A1:C5="speedboat";SPALTE(column!A:C))))

Converted formula:

=INDEX(column!A1:C1,,MIN(IF(column!A1:C5="speedboat",COLUMN(column!A:C))))

Currently the result of the above formula is Car. If I use the evaluate formula tool I can see, that the IF clause isn't evaluating the whole matrix, but I don't know why. What I'm doing wrong?

How can I get the header of the column?

Upvotes: 1

Views: 1173

Answers (1)

JvdV
JvdV

Reputation: 75990

The problem is you are using an array formula that needs to be confirmed through CtrlShiftEnter. You need to only do that once, and you are able to drag the formula.

If you don't want to enter through CtrlShiftEnter, an alternative to your formula could be:

=INDEX(column!A1:C1,SUMPRODUCT((column!A2:C5="speedboat")*(COLUMN(A2:C5))))

Or maybe even better:

=INDEX(column!A1:C1,MAX(INDEX((column!A2:C5="speedboat")*(COLUMN(A2:C5)),)))

Upvotes: 2

Related Questions