Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to define certain things in excel like when I type some code(ex 112) in a new sheet, it will automatically import data from a separate sheet and display the contents of it in other columns.


112    agarwal male
119    reena   female

That is, when i type 112 in a column, it will automatically display agarwal and male in the same row but in different columns(The whole data from where it will import is present in an another sheet).

share|improve this question
Sounds like you need a database, not a spreadsheet. – Kruug Feb 13 '13 at 18:21
I want to create a database and then want to use it in excel – neeraj Feb 13 '13 at 18:29
yes , from a seperate dedicated excel sheet – neeraj Feb 13 '13 at 18:31
Are you familiar with VLOOKUP ? – F106dart Feb 13 '13 at 20:06

Use of the IF statement in a formula will do what you seek. The IF function checks for a logical value (true or false) then takes action on that result.

IF(logical_test, [value_if_true], [value_if_false])

So, by having it check the "code" it will know what values to place in the cells of other columns. If you are typing the "code" in A1, then put this formula in B1


Place this formula in C1


As you can see, the first one checks for the code 112, if it's TRUE it places the word "agarwal" in the cell. If it is False it goes to the next instructions which happen to be nested IF statement. At the end of the statement it will leave the cell blank if neither returns TRUE.

You can copy the formulas down the rows by double clicking the lower right corner.

share|improve this answer
thanks for your suggestion.Actually i am having a separate sheet which contains all the data.What I want is if i write the first value(ex 112) in another separate sheet, i want it to automatically import all that data from that sheet. – neeraj Feb 13 '13 at 18:44
You may want to edit your question with those details. My answers was based on the question you asked. It is important to include as much detail as possible when asking your questions. I will update my answer accordingly. – CharlieRB Feb 13 '13 at 18:47

Using just excel and formula's, no VBA etc (EDIT - this will only work in Excel 2007 or later)...

In a document called Book1.xls you have the following table containing your base information - NO FORMULAS:

enter image description here

Then in a document called Book2.xls you have the table you want to auto update.

In Cell A1 you put the number you are searching for (in this case 112). In Cell B1 place the following code:


This will look up the contents of Cell A1 in the area between A1 and C2 in Sheet1 of document Book1.xlsx (note the documents in this case need to be in the same folder) and if it finds them it will display the contents of the cell with col index = 2 (the col index starts at number 1 at the first column in your array)

In this case your array is the contents from A1 to C2 but the information you are looking for will always be in Column A so col index = 1 is Column A, col index = 2 is Column B and col index = 3 is Column C.

In Cell C1 replace the last 2 in the above formual with a 3:


Now, enter a number in Cell A1 in your sheet and if it matches a number somewhere in the array A1 to C2 in your reference document then the contents of cells B1 and C1 will automatically be updated with the name and sex of the matched item.

share|improve this answer
A solution without VBA is of course always better, but let me kindly add two notes: =IFERROR isn't available in Excel 2003 and the OP forgot to mention which version he is using. Also you need to copy the formula around to every column you want to populate. – nixda Feb 14 '13 at 10:14
TY for your comment, I was not aware IFERROR was not available pre-2007 but you are correct, I have edited my answer to indicate this. – Kevin Anthony Oppegaard Rose Feb 14 '13 at 12:10
@nixda IFERROR may be replaced by IF(ISERROR(...)) combination for earlier versions. – Peter L. Feb 14 '13 at 15:29
Thank you very much is working and my problem is solved...:) – neeraj Feb 14 '13 at 17:11
@neeraj You should mark the answer which solved your problem as accepted with a green check mark – nixda Jul 11 '13 at 20:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .