How to create vlookup for different variables?

QuestionEdit

Hi Mr. Ogilvy

I hope you would be willing to help me on this case, since I really trust your expertise.

Acoording to the following rawdata:

WK CATEGORY NAME X1 X2 X3 1 A JOHN 456 45 6 A MAX 326 46 3 B JOHN 398 49 5 B MAX 784 49 7 B DENNIS 984 46 8 2 A JOHN 632 36 9 A PETER 235 46 2 A MAX 124 89 5 B MAX 965 50 9 B DENNIS 365 36 6 B MEGAN 321 65 5 3 A JOHN 458 31 8 A PETER 284 64 8 B JOHN 985 86 4 B PETER 489 95 1

I need to complete the following chart by being able to find the "?" values according to previous data:

WK CATEGORY NAME X1 X2 X3 1 A JOHN ? ? ? 1 B JOHN ? ? ? 2 A JOHN ? ? ? 2 B JOHN ? ? ? 3 A JOHN ? ? ? 3 B JOHN ? ? ? 1 A PETER ? ? ? 1 B PETER ? ? ? 2 A PETER ? ? ? 2 B PETER ? ? ? 3 A PETER ? ? ? 3 B PETER ? ? ?

My guess is I will need to adapt a "VLOOKUP" funtion to lookup first by "WK", then by "CATEGORY", then by "NAME" and finally to pickup the required "X" value. Of course if you create a better way it will be more than wellcome.

Diego.

AnswerEdit

Diego,

first you data is all compressed together, so I assume that week number is only present for the first entry in that week, so column A in the original data is like

1

2

3

It seems to me you could select column A, then do Edit=>Goto, select special, then blanks.

that should select all the emtpy cells and the active cell would be the cell beneath the "1" digit. Assume the 1 is in cell A2 and of all the selected cells, the activecell is A3

go to the formula bar and enter a relative formula =A2 then instead of doing just enter, do Ctrl+enter. This fills all the Empty cells with the week number. Now Select column A and do Edit=>copy, then immediately Edit=>paste Special and select values.

this removes the formulas. Now you can sort the data on Name, then Week, then Category (or whatever sequence will give you the order you need).

You could do all this a copy of your data so you don't modify the original.

Would that give you what you want?

If you want to go with a vlookup, in the first data set, I would go to F2 (I assume 1 A JOHN 456 45 6 is in A2:E2)

I would put in a formula =if(A2="",F1,A2)

then drag fill that down the column.

then in G2 I would put in the formula

=F2&B2&C2 and drag fill that down the column.

then over in the other set of data assume the first ? is in D2, and the first set of data is in Sheet1, then in D2 of the second sheet I would put

=Index(Sheet1!D:D,match($A2&$B2&$C2,Sheet1:$G:$G,0),1) then drag fill that to column E

then select D2:E2 and drag fill down the columns.

Hopefully one of those will work for you.