Ubuntu
openoffice.org package

OpenOffice Spreadsheet vlookup problem

Asked by GP Das

vlookup function is not working like MS-Excel.
Suppose in sheet1 I have 3 names with age.
In sheet2 I have Name, class and age in 3 columns respectively.
The age column is blank in sheet2. In sheet2 names are repeated randomly.
Now I want to fill up the age column using vlookup function with reference to
sheet1. I am explaining elaborately.

Sheet1
Name | Age
---- -------------
Name-A | 22
Name-B | 24
Name-C | 25

Sheet2
Name | Class | Age
Name-A | 10 | |
Name-A | 11 | |
Name-A | 12 | |
Name-B | 10 | |
Name-B | 11 | |
Name-C | 10 | |
Name-A | 14 | |

Now I want to fill up the 'Age' column using 'vlookup' function.
Thanks.

Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu openoffice.org Edit question
Assignee:
No assignee Edit question
Solved by:
Tony Pursell
Solved:
Last query:
Last reply:
Revision history for this message
Tony Pursell (ajpursell) said : 		#1

Hi

Please tell us which version of Ubuntu and OpenOffice.org you are using.

Please give us the formula that you are using in the Age column of Sheet 2.

Thank you.

Tony

Revision history for this message
Best Tony Pursell (ajpursell) said : 		#2

Hi Again

If your layout is exactly the same as you describe in your question, then the VLOOKUP formula should be:

=VLOOKUP(A2,Sheet1.$A$3:$B$5,2,0)

in cell Sheet2.C2. You can then copy this into the rest of column C. Note that the range of the lookup table - Sheet1.$A$3:$B$5 - must be fixed, not relative, or it will be changed as you copy it,

Tony

PS If this answers your question, please mark it as Solved.

Revision history for this message
GP Das (gpdass) said : 		#3

Thanks Tony Pursell, that solved my question.

Revision history for this message
GP Das (gpdass) said : 		#4

In stead of writing a formula manually like =VLOOKUP(A2,Sheet1.$A$3:$B$5,2,0) can I do it by clicking mouse in the 'Formula window' that comes when we click on the 'function'. In the 2nd 'Textbox' -> Lookup Array of the function box it comes like =VLOOKUP(A2,Sheet1.A3:B5,2,0). In case of MS Excel we can click on column header(A) and drag it to (B) which takes care of this and the formula looks like =VLOOKUP(A2,Sheet1.A:B,2,0). If any thing can be done this way will be highly appreciated.

Revision history for this message
Tony Pursell (ajpursell) said : 		#5

Yes, you can use the Function Wizard to create a VLOOKUP formula. That will work for the cell that you create it in. The problem is that the range for the lookup array (A3:B5) uses relative cell references and not fixed references. Consequently, if you copy the formula to another cell they will be changed by the relative difference between the two cells. I don't have Excel, so I cannot test it, but from what you tell me, Excel gives you relative references as well. However, it is a simple job to edit the formula to add the $ signs before you copy it.

Tony