August 14, 2010

Vlookup in excel to get value from other sheet and use of instr function

Vlookup in excel to get value from other sheet and use of instr function

Vlookup example


 =IF(ISERROR(VLOOKUP(J5,Designation!$B$2:Designation!$D$130,3,FALSE)),"",VLOOKUP(J5,Designation!$B$2:Designation!$D$130,3,FALSE))  
 =IF(ISERROR(VLOOKUP(G6,EmployeeDetails!$E$2:$F$18,2,FALSE)),"",VLOOKUP(G6,EmployeeDetails!$E$2:$F$18,2,FALSE))  
 Public Function scut(ctxt As String) As String  
 Dim ind As Integer  
 ind = InStr(ctxt, "0")  
 Dim rstr As String  
 rstr = Mid(ctxt, 1, ind - 1)  
 scut = rstr  
 End Function  

This is simple example of Vlookup. How to use vlookup in Excel worksheet.

 =trims(C2)  
 =IF(ISERROR(VLOOKUP(TRIM(B2),EInfo!$E$2:$J$470,6,FALSE)),IF(ISERROR(VLOOKUP(TRIM(B2),EInfo!$F$2:$J$470,5,FALSE)),IF(ISERROR(VLOOKUP(TRIM(B2),EInfo!$G$2:$J$470,4,FALSE)),IF(ISERROR(VLOOKUP(TRIM(B2),EInfo!$H$2:$J$470,3,FALSE)),IF(ISERROR(VLOOKUP(TRIM(B2),EInfo!$I$2:$J$470,2,FALSE)),"",VLOOKUP(TRIM(B2),EInfo!$I$2:$J$470,2,FALSE)),VLOOKUP(TRIM(B2),EInfo!$H$2:$J$470,3,FALSE)),VLOOKUP(TRIM(B2),EInfo!$G$2:$J$470,4,FALSE)),VLOOKUP(TRIM(B2),EInfo!$F$2:$J$470,5,FALSE)),VLOOKUP(TRIM(B2),EInfo!$E$2:$J$470,6,FALSE))