CODEWITHSUNDEEP
excelvlookup
Novice
Novice

Reputation: 535

Excel vlookup from an aggregated group

I have a sheet of data.Sheet 1:

enter image description here

and I want to populate the following sheet:

enter image description here

Here, number of projects are fixed to be 7 for any customer. So for each customer I need to fill the latest status of all the projects. So if any customer has only got 1 project but many statuses the second sheet will only be updated for customer 1 in proj1 and the last status for that project is needed. Rest of the projects remain empty. For a customer who has 3 projects associated, I need to fill the data for proj1, proj2, and proj3. For each of these I need to find the latest status.

The entries in shee1 are not sorted, but for clarity I have put them in sorted order here.

Dont know how to accomplish this. I tried vlookup but it is limited in giving the first entry and ignores the rest. Tried to search on how to group or aggregate in excel but couldnt find anything suitable.

Upvotes: 0

Views: 592

Answers (2)

basic
basic

Reputation: 11968

You can use array formula with INDEX/MATCH:

{=INDEX($C$2:$C$12,MATCH(1,($A$2:$A$12=$H2)*($B$2:$B$12=I$1)*($D$2:$D$12=MAX(IF(($A$2:$A$12=$H2)*($B$2:$B$12=I$1),$D$2:$D$12))),0))}

If you want a blank cell instead of an N/A error, use IF IFERROR:

{=IFERROR(INDEX($C$2:$C$12,MATCH(1,($A$2:$A$12=$H2)*($B$2:$B$12=I$1)*($D$2:$D$12=MAX(IF(($A$2:$A$12=$H2)*($B$2:$B$12=I$1),$D$2:$D$12))),0)),"")}

enter image description here

Array formula after editing is confirmed by pressing ctrl + shift + enter

Upvotes: 0

Stachu
Stachu

Reputation: 1724

VLOOKUP doesn't work with multiple columns , you will need a single helper key column to use it. You can do the following:

1) create a helper key column in your first sheet, before the Client column, that would be concatenation of Client&Project, so it would have entries like C1p1 and C4p3

2) in the second sheet add codes for the Project above your output table, so that the cell above Proj1 would be p1, the one above Proj2 p2, etc.

3) in the output sheet use the following formula (adjust the ranges, in this formula I assume that the customers are in Column A and Projects are in 2nd row, with respective project keys in the first row)

=VLOOKUP($A3&B$1,Sheet1!$A$2:$D$13,4,0)

$ signs are relevant as they keep the reference locked to the first column and row

Upvotes: 0

Related Questions