I have the below data in sheet 1, where as in column A its the activity and column b its the name of person who has done it. and the names will change daily.
Sheet 1
A B C D E F
1 Activity Name
2 12364 Saneel
3 6543 Karthik
4 7841 Somen
5 9654 Deepak
6 2541 Saneel
7 65489 Hema
8 4567 Sachin
9 9135 Karthik
I have a standard format in sheet 2 where i need the result as below -
Sheet 2
1 A B C D E F
2 Saneel 12364 2541
3 Karthik 6543 9135
4 Somen 7841
5 Deepak 9654
6 Hema 65489
7 Sachin 4567
8
I will have fixed the names in cell, I need the column B and C to populate from sheet 1 based on who has done the activity.
vlookup would be my choice here. the catch is, in order for it to work, you need the names on sheet 1 to come before the numbers. is it possible to switch the orders of the columns on sheet 1 so that the names come first?
How would a VLOOKUP work here?
My understanding in Sheet2 is empty so there is nothing to compare against.
Plus, the result has TWO columns of numbers.
the post says "I will have fixed the names in the cell" for sheet 2, so the names can be used as the lookup value. the vlookup would then insert the numbers from sheet 1 according to the name matches. The point here (as I understood it) was to take the randomly ordered list from Sheet 1 and sort the activity numbers to a fixed list of names on sheet 2.
Return MULTIPLE corresponding values for ONE Lookup Value, Horizontally, in one Row
=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), COLUMN(A1)))
With the same example used in the website
- Posts
- 10
- Registration date
- Wednesday December 24, 2014
- Status
- Member
- Last seen
- January 13, 2015
-The title is a little misleading as the VLOOKUP command is not used in the solutions given.
The very last example will work for this question though.
I too thought of using a pivot table but couldn't get it to format as required. How would you do it?
- Posts
- 3
- Registration date
- Tuesday January 13, 2015
- Status
- Member
- Last seen
- January 15, 2015
> tlynnec- Posts
- 10
- Registration date
- Wednesday December 24, 2014
- Status
- Member
- Last seen
- January 13, 2015
-I tried with thi but i ahve an error - #VALUE!, Kindly advise
- Posts
- 3
- Registration date
- Tuesday January 13, 2015
- Status
- Member
- Last seen
- January 15, 2015
-- Posts
- 3
- Registration date
- Tuesday January 13, 2015
- Status
- Member
- Last seen
- January 15, 2015
> tlynnec -Return MULTIPLE corresponding values for ONE Lookup Value, Horizontally, in one Row
=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), COLUMN(A1)))
With the same example used in the website
- Posts
- 3
- Registration date
- Tuesday January 13, 2015
- Status
- Member
- Last seen
- January 15, 2015
-Thanks in advance.