Do I want to use the vlookup?

Closed
AMSTARR - Sep 16, 2009 at 06:36 PM
 amstarr - Sep 25, 2009 at 08:07 AM
Hello,
I am tryihg to return data from a table array in sheet2 by comparing the values in sheet 2 (say column B & column D) to the values on sheet 1 (say columns 19 & 23) and return the value if the both match exactly from column 29.
I have duplicated number identifiers for (pigs) in my table and the other column is to compare the dates that they were born. If the same it will tell me the pigs sex.
Please ask me more questions if this makes no sense.
Thanks

4 replies

The order of the data on both sheets are the same, so you could just use copy/paste.

But I guess this is not always the case.

I noticed that the data separating the pigs are the dates.
You could use Vlookup to retreive the remaining data.

Put the following formula in cell E2 on sheet 2
=Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,2) Change the 8 from $G$8 to the number of your last row.
Drag the formula down as far as necessary.

Select and copy the fomula and paste it into cell F2. Then add one on the last number:
=Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,3) and drag the formula down.
If you drag the formula to the right be sure that the first part of the fomula is still Sheet2!D2.

Repeat for the remaining 3 columns.

G2: =Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,4) and drag the formula down.
H2: =Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,5) and drag the formula down.
I2: =Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,6) and drag the formula down.

Best regards,
Trowa
1
Thank you so much for all you help. I will work with this formula and I am sure it will work great. Thanks
0
So if the data on sheet 2 in column B & D is the same as sheet 1's column S & W you would like in another column to be put "male" (for example) and "female" if they are not the same.

=IF(sheet2!B1=sheet1!S1,IF(sheet2!D1=sheet1!W1,"Male","Female"),"Female")

I hope I understood you correctly.

Kind regards,
Trowa
0
Hi Trowa thanks for responding.
I actually need it to return the value that is in column 29. The sex would be Barrow, Guilt, Sow & Boar. So instead of one or the other it would actually need the result in that column.
I hope that makes sense.
Thanks again
0
Hi Amstarr,

You told that there are two conditions, one where the data matches and one where they don't.
Based on that I assumed there are two sexes.

Now you say there are 4.

My question to you is what the other conditions are?

In which case would the result be Barrow?
In which case would the result be Guilt?
In which case would the result be Sow?
In which case would the result be Boar?

To clarify the situation, maybe you could give me an example of how your sheet looks like.

Best regards,
Trowa
0
Sheet 1

A B C D E F G
1 Ear Tag Test Date Age Sex Sire Dam SOD
2 68 6/18/2008 4.1 Sow Sisu 4 Cher 4 Karlstorp 8
3 68 4/23/2008 1.9 Boar Karlstorp Cher 5 Karlstorp 1
4 68 4/11/2008 3.6 Sow Iron Man 6 Kate 3 Karlstorp 8
5 69 6/19/2008 3.1 Sow Samson 2 Cher 3 Pikkus 6
6 69 4/11/2008 5.2 Sow Pikkus 6 Special 2 Karlstorp 8
7 70 6/10/2008 2.6 Sow Samson 2 Kate 3 Karlstorp 8
8 70 5/21/2008 3.4 Sow Pikkus 6 Cher 4 Karlstorp 9

Sheet 2

A B C D E F G H I
1 Pig # Tag # Test Result Test Date Age Sex Sire Dam SOD
2 772 68 Good 6/18/2008
3 774 68 Poor 4/23/2008
4 778 68 Excellent 4/11/2008
5 779 69 Good 6/19/2008
6 780 69 Good 4/11/2008
7 782 70 Excellent 6/10/2008
8 783 70 Poor 5/21/2008

On sheet 1 I named the table array Ear_Tag.

This is information that is provided to me from the farmers.

Note that there are sometimes more the 1 pig with the same tag number. The only way to tell them apart is by the Test Date.

On Sheet
Is where I record the test results.

I dont want to have to look up each pig after each test and fill in the rest of the cells manually. I would like to have a look up of the ear tag and the test date to find the correct pig. Then each of the cells will fill in the correct information.

I hope that clarifies the question better.
Thanks
0