Using repeating values in a column for looking up values in rows

vinaylak · 10-29-2009, 11:32 AM

Hello
I have a set of 2 sheets. Sheet1 is called data collation and sheet2 is called broad plan.
On broad plan I have a whole list of all the trainings we offer in the first column and against each course people enter their names. Only one person attends a course at a time. So the same person may enter his/her name into multiple course rows.
Ok, so this is what I need. On Sheet1, I have a course requirements listing where the names of people are on the first column. Column2, column 3 etc have titles training1, training2 etc and are supposed to be populated with the course names they have put their names into on sheet 2.
I tried the following formula on the training1 column of sheet1 and it gave me the correct 1st requests for all the people whose names are listed in column 1.
=INDEX('Broad Plan'!$B$4:$F$87,MATCH($A2,'Broad Plan'!$F$4:$F$87,0),1)
But for training 2 column, the search should detect the second occurance of the name and training 3 column should show the result for the 3rd occurence of the name if any and so on. This is where I am failing.
Can anyone please suggest an easier way to do this or may be a way to track the previous match location so that the new search range can be one row below the matched row?

Thanks in advance.
Lakshmi Vinay

**macropod** · 10-29-2009, 07:52 PM

Hi Lakshmi Vinay,

See the attachments to my posts in:
Excel Formula Issues

10-29-2009, 11:32 AM	#1 (permalink)
vinaylak Registered User Join Date: Oct 2009 Posts: 1 OS: WINDOWS XP	Using repeating values in a column for looking up values in rows Hello I have a set of 2 sheets. Sheet1 is called data collation and sheet2 is called broad plan. On broad plan I have a whole list of all the trainings we offer in the first column and against each course people enter their names. Only one person attends a course at a time. So the same person may enter his/her name into multiple course rows. Ok, so this is what I need. On Sheet1, I have a course requirements listing where the names of people are on the first column. Column2, column 3 etc have titles training1, training2 etc and are supposed to be populated with the course names they have put their names into on sheet 2. I tried the following formula on the training1 column of sheet1 and it gave me the correct 1st requests for all the people whose names are listed in column 1. =INDEX('Broad Plan'!$B$4:$F$87,MATCH($A2,'Broad Plan'!$F$4:$F$87,0),1) But for training 2 column, the search should detect the second occurance of the name and training 3 column should show the result for the 3rd occurence of the name if any and so on. This is where I am failing. Can anyone please suggest an easier way to do this or may be a way to track the previous match location so that the new search range can be one row below the matched row? Thanks in advance. Lakshmi Vinay

10-29-2009, 07:52 PM	#2 (permalink)
macropod TSF Enthusiast Join Date: Apr 2008 Location: Australia Posts: 559 OS: Vista	Re: Using repeating values in a column for looking up values in rows Hi Lakshmi Vinay, See the attachments to my posts in: Excel Formula Issues __________________ Cheers macropod (MS MVP -Word) Last edited by macropod; 10-29-2009 at 07:53 PM.