Related:

- Do I want to use the vlookup?
- Vlookup if cell contains partial text ✓ - Excel Forum
- Vlookup if cell contains a word within text ✓ - Word Forum
- Lookup partial text contained in cell range - Office Software Forum
- Transfer data from one excel worksheet to another automatically vlookup - Guide
- Vlookup subtraction formula ✓ - Office Software Forum

## 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$

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$

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$

H2: =Vlookup(Sheet2!D2,Sheet1!$B$2:$G$

I2: =Vlookup(Sheet2!D2,Sheet1!$B$2:$G$

Best regards,

Trowa

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

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

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

I hope I understood you correctly.

Kind regards,

Trowa

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

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

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

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

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

Sep 25, 2009 at 08:07 AM