Vlookup formula

Solved/Closed
priya - May 3, 2010 at 08:21 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- May 3, 2010 at 12:49 PM
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



9 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
May 3, 2010 at 09:27 AM
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.
0
vishnugj
Posts
10
Registration date
Monday May 3, 2010
Status
Member
Last seen
April 14, 2015

May 3, 2010 at 09:46 AM
hi, i have uploaded same file.
Please use link https://authentification.site/files/22249358/example.xls
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
May 3, 2010 at 10:03 AM
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
0
vishnugj
Posts
10
Registration date
Monday May 3, 2010
Status
Member
Last seen
April 14, 2015

May 3, 2010 at 11:45 AM
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.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
May 3, 2010 at 11:51 AM
Could you post the book where it is NOT pulling the data. One has to see the data to tell you the answer. there Could be space issue, non-printing character etc. So unless one see the data, hard to tell you. Post the file and paste thelink back here as you did before
0

Didn't find the answer you are looking for?

Ask a question
vishnugj
Posts
10
Registration date
Monday May 3, 2010
Status
Member
Last seen
April 14, 2015

May 3, 2010 at 12:01 PM
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
May 3, 2010 at 12:13 PM
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
0
vishnugj
Posts
10
Registration date
Monday May 3, 2010
Status
Member
Last seen
April 14, 2015

May 3, 2010 at 12:28 PM
sorry to trouble you again. I followed the above steps, still i could not get the datas.

Can you please look into it again ?
0
vishnugj
Posts
10
Registration date
Monday May 3, 2010
Status
Member
Last seen
April 14, 2015

May 3, 2010 at 12:31 PM
sorry for the above reply. It works. i did not see it fully.
0
vishnugj
Posts
10
Registration date
Monday May 3, 2010
Status
Member
Last seen
April 14, 2015

May 3, 2010 at 12:36 PM
Thank you very much for your help. It works.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
May 3, 2010 at 12:49 PM
glad it worked out
0