CODEWITHSUNDEEP
excelvbaindexingmatchvlookup
lazer
lazer

Reputation: 79

VLOOKUP horizontally and IF MATCH do OFFSET

so I was wondering if you have an idea how to solve this problem. I basically want to create a sheet in Excel, which allows me to display the upcoming dates of a milestone and hence the responsible person of a certain project. Please find the structure of the table below.

Some explanations:

Column A: Represents the name of the project

Column B: Shows the next date of the upcoming milestone. I solved this by a simple max-function (MAX(D3;F3)).

And here comes the main problem:

Column C: This column should automatically show who is the next responsible person considering the date, which is displayed in column B. I thought that this could somehow be solved by a vlookup in combination with an offset function. However, vlookup needs an argument for a column, which I can not provide. I also tried some match and index combinations, which did not work.

Can this problem be solved without using VBA? If yes, how can I approach a solution here. Second, if VBA is necessary how would the function look like?

Help would be much appreciated!

Thanks in advance.

A B C D E F G
1 Next Date Next Resp. Person Milestone_1 Resp. Person Milestone_2
2 Project A 25.05.2021 Tom 25.05.2021 Tom
3 Project B 12.03.2021 Luca 23.02.2021 Laura 12.03.2021
4 Project C 23.04.2021 Laura 23.04.2021 Laura

Upvotes: 0

Views: 272

Answers (1)

teylyn
teylyn

Reputation: 35935

You can use an Index/Match combination. Start in cell C2 with

=index($D2:$Z2,match($B2,$D2:$Z2,0)+1)

Note how the references only use $ signs in front of the column letter, not the row. When the formula is copied down, the row number will adjust to the current row.

The Index part looks at columns D to Z in the current row. The nested Match returns the position of the found date in that range. Add 1 to that to arrive at the name, i.e. offset the found position by adding 1.

Upvotes: 2

Related Questions