vlookup formulaSolved/Closed

Question

Hi, 

Could you please anyone help me in this? 

(column headings : Roll No. ,  correction date , Type , code , rank ,  Marks , exam date , time , comments )

TABLE A         
         
Roll No.     correction date  Type  code  rank    Marks     exam date     Time       Comment 
1234567    15/04/10           XXX    6        3          66         20100413     940        average 
8901234     15/04/10          XXX    6        2          80         20100413    1030       very good 
5678901     15/04/10          YYY    6        4           20         20100413     930        fail 
2345678     15/04/10          YYY    5                                  20100413     900  
90123456    15/04/10         YYY     6       1          100        20100413    100         excellent 


I have two tables TABLE A and TABLE B. 

TABLE A already have some information where these datas has to be updated in TABLE B once we just enter roll no in TABLE B. 

TABLE B has some roll number which match Roll No. in TABLE A and rest  all different roll numbers. 

Once i copy paste roll nos. in table B, it should pull the data from table A and display in Table B in corresponding columns, however it should display blank if the roll nos. in table A and Table B does not match and if some cell is blank without datas. 


TABLE B        
        
Roll No: Correction date Marks exam date Code rank Time Comment 
8901234        
90123456        
1478523        
41258963        
1234567        
852147963        
1235879        
54672893        
90123456        
35789510        
951035741        
2345678        
5678901        



Configuration: Windows XP / Firefox 3.6.3

rizvisa1 · Answer

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.

vishnugj · Answer

hi, i have uploaded same file.
Please use link https://authentification.site/files/22249358/example.xls

rizvisa1 · Answer

This is to pull up the date. you will need to do same for other columns too    

=IF(ISERROR(VLOOKUP($K4,$A:$I,2,FALSE)),"",VLOOKUP($K4,$A:$I,2,FALSE))    


Let me explain this part    
VLOOKUP($K4,$A:$I,2,FALSE))    


$K4    
I am going to look for value found in K4 (8901234 as per your sample)    

$A:$I    
I am going to search and locate my answer in range A:I (column A thru I). By default what i am looking for (8901234) has to be in first column of search (column A in this case)    

2:    
If what i am looking for (8901234) is found in first column of search range (a:i), then return the value found on same row but in the second column of range A:I, which would be column B in this case    


False    
i want exact match    

IsError part is to handle if the match in not found, give me "" instead of default #N/A

vishnugj · Answer

Thank you for your replies. 

It works when i typed each roll number manually, however table B  roll numbers  has been downloaded from access database ,when i copy paste it ( even paste special values ) it does not pulls up the data's but when typed numbers manually it works. 

Table A data's has been downloaded from an excel. if i copy any roll number from table A and paste in table B roll number it displays values.  

Is this because roll number taken from access database ( view -> data view ), it doesn't pull data's in table B ? 

Please advice.

vishnugj · Answer

hi please use this link
https://authentification.site/files/22251971/example.xls

rizvisa1 · Answer

Yeah as I suspected. It is issue of format. The value in column A are numbers however the column J is text. You have to do one extra step to make every thing work. 

This should work, if not see the detail step by step after wards
1. Select Column J
2. Goto Data and choose "Text to Column" feature
3. Choose delimited
4. Click Finish

If the above did not work, then do this step by step
1. Select Column J
2. Goto Data and choose "Text to Column" feature
3. Choose delimited
4. Click next
5. remove all check marks
6. click on next
7. click on "general" 
8. click on finish

vishnugj · Answer

sorry to trouble you again. I followed the above steps, still i could not get the datas. 

Can you please look into it again ?

vishnugj · Answer

sorry for the above reply. It works. i did not see it fully.

vishnugj · Answer

Thank you very much for your help.  It works.

Vlookup formula

9 replies

Subscribe To Our Newsletter!