VLOOKUP second value [SOLVED]

[Shayeebur]

Hi Chandoo

This is Shayeebur, I have made an Attendance sheet in which two same names are repeated but both are different people with different team names now look at excel, in first name Cindy Albert belongs to Team no. 401 & second Cindy Albert belongs to team no 406. Now i tried normal vlookup method in which it is picking up the first Attendance for the names as UAL but it should be P for second Cindy Albert as it belongs to 406. The other thing is i tried your method as you informed us to take as per combining the lookup value here it is name&team no(=VLOOKUP(C4&E4,Sheet1!$B$3:$F$19,5,0) as per this lookup value i am getting #N/A answer kindly suggest the answer for this.


Note :Vlookup has done from Sheet 1 to Sheet 2 the second thing is in Sheet 1 column E is hidden as per my requirement in which I will be putting Team Managers name later on.


http://www29.zippyshare.com/v/81926685/file.html

 

[Sam Mathai Chacko]

Use =INDEX(Sheet1!$F$3:$F$19,MATCH(Sheet2!C4&Sheet2!E4,Sheet1!$B$3:$B$19&Sheet1!$D$3:$D$19,0)) as an array formula

 

[Sam Mathai Chacko]

I'm not sure what is the method by Chandoo that you are referring to above, but I guess it could be that he asked you to combine the values of the two columns in to one column (to the left of the table), and then do a vlookup by combining each row cells in those columns.


So in Sheet1 A3=B3&D3, and drag down


Then in Sheet2 G4=VLOOKUP(C4&E4,Sheet1!$A$3:$F$19,6,0) and drag down.

 

[inbp]

Hi Shyeebur!


Please visit the below link


http://chandoo.org/wp/2010/11/10/vlookup-second-value/

 

[Shayeebur]

Thanx Sam Mathai Chacko


i was not aware that i need to add one more helper column that was the mistake i was making. thanks a lot.