CODEWITHSUNDEEP
excelvba
Russbear
Russbear

Reputation: 1261

Excel - get the column number based on multiple criteria

I have headings separated onto different rows. Instead of having a heading that makes sense and is unique like "Litres of water dumped on Tuesday" I have:

Litres of
water
dumped
on Tuesday

separated across 4 rows.

I want to know how much water was dumped on Tuesday and there are multiple columns that say each of "Litres of", "water", "dumped" and "on Tuesday". Is there an excel function that will help me do this? Do I need to go use macros?

Upvotes: 0

Views: 1216

Answers (2)

Jean-François Corbett
Jean-François Corbett

Reputation: 38540

If I understand correctly, you need the entire header to be in a single cell, as opposed to split over four rows in four parts that have no useful meaning individually.

You can do this using Excel's CONCATENATE function or, equivalently, the more succint & operator. Example:

enter image description here

Now you have your unique header and you can do whatever you want with it.

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149325

Is this what you are trying? I am assuming that the data is arranged as shown in the snapshot.

=OFFSET(INDIRECT(CELL("Address",INDEX(A1:D4,MATCH(TRUE,COUNTIF(OFFSET(A1:D4,ROW(A1:D4)-CELL("Row",A1:D4),0,1),F1)>0,0),MATCH(F1,INDEX(A1:D4,MATCH(TRUE,COUNTIF(OFFSET(A1:D4,ROW(A1:D4)-CELL("Row",A1:D4),0,1),F1)>0,0),0),0)))),1,0)

This is an array formula i.e you have to press CTL+SHIFT+ENTER after you enter the formula

SNAPSHOT

enter image description here

Upvotes: 0

Related Questions