Find and copy

[Eric]

Hello all,
I am trying to post a cell into sheet that has 1,000 lines to it. My
problem is it must find 3 criteria's before posting.
EX:
sheet 1 is layed out like this
A B C D
date Job Mix
2/2/09 1 9.5
2/2/09 2 12.5
2/22/09 1 9.5


The information to be posted is found on sheet 2

A B C D
date Job Mix Tons
2/2/09 2 12.5 200 <~~I will imput this information

Now what I want is for Sheet 2 Column D to post on Sheet 1 Column D after it
has found Columns A, B, & C from sheet 2.

I hope this makes sense. Thank you for your help.

 

[Jacob Skaria]

You can use a formula in Sheet2 Column D

In D2 and copy dow
=INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$A$2:$A$100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C$2:$C$100=C3),0))

To handle the N/A# you can modify the formula as below
=IF(ISNA(formula),"",formula)

If this post helps click Yes

 

[Jacob Skaria]

If you are particular about a VBA solution try the below which run on Sheet1.


Sub Macro()
Dim lngRow As Long
For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
res = Evaluate("INDEX(Sheet2!$D$2:$D$100,MATCH(1, " & _
"(Sheet2!$A$2:$A$100=A" & lngRow & ")*(Sheet2!$B$2:$B$100=B" & _
lngRow & ")*(Sheet2!$C$2:$C$100=C" & lngRow & "),0))")
If Not IsError(res) Then Range("D" & lngRow) = res
Next
End Sub


If this post helps click Yes

 

[Eric]

Hello Jacob,

The formula isn't working and maybe it's because I am doing something wrong
or didn't explain it well enough. I keep getting a #N/A error. I have tried
different approaches but to no avail.

The one row of information on sheet 2 is the only line that sheet 2 will
have. So I am wanting to find the information in columns A, B, C on sheet 2
on sheet 1's columns A, B, C.
When that is done I then want the information in column D from sheet 2 to be
pasted in to sheet 1's column D in the same row where columns A, B, C, match
sheet 2's columns A, B, & C

Any more help would be of great appreciation. Thank you...

Eric

 

[Jacob Skaria]

Hi Eric

Hope you have tried the macro

I have missed to mention that this is an array formula. You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

=INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$A$2:$A$100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C$2:$C$100=C3),0))

To handle NA# try the below..OR try the macro

=IF(ISNA(INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$A$2:$A$100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C$2:$C$100=C3),0))),"",INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$A$2:$A$100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C$2:$C$100=C3),0)))

 

[Eric]

Good Morning Jacob,

I was able to finaly figure out the Array. What I think I was doing
incorrectly was using the index as a single cell not a group of cells.
Anyway it works but it isn't keeping the number on sheet 1. Everytime I
change the information on sheet 2 the previous find will go to #N/A.

sheet 1 is layed out like this
A B C D
date Job Mix Tons
2/2/09 1 9.5 200
2/2/09 2 12.5 #N/A <~~ Found First and was correct
2/22/09 1 9.5

The information to be posted is found on sheet 2
A B C D
date Job Mix Tons
2/2/09 1 9.5 200 <~~Second find


Now if I go to another job the 200 in column D on sheet 1 will go to #N/A.
Basicly I need the number to stay on sheet 1 in column D after is it found
even when I am searching for another job, mix, or date.

Eric