Thanks Formula searches the way I need
But for some reason it is not Inputting the Correct Information into AG



Cells
=INDEX(Sheet1!$AG$1:$AG$8000,MATCH(1,($D$1:$D$8000 =Sheet1!D1)*($J$1:$J$8000**=Sheet1!J1),0))



With the data I have it will not be in the same order all the time and
items and lines will be removed and added. I think this code is for if
the line stay the same


For example
On Sheet 1
D= Account Number
J= Product Code
AG= Notes


Sheet 2 is the same
D= Account Number
J= Product Code
AG= Notes


But the information inside the cells will mostly be different
And in different order.
What I needs that if Sheet1 (D & J) upto 8000 lines = (Sheet2 (D & J)



upto 8000 lines then Sheet 2 (AG) = Sheet 1 (AG)


Sorry hard to explain
In a nut shell
Sheet1 (D&J) LINE 5)
Sheet2 (D&J) LINE 800)
Both Match I have notes typed In AG(Sheet1)
Now I need these notes to be shown in Sheet2(line 800) now?


Reply

I don't understand what those final -- are doing.

=INDEX(Sheet1!$AG$1:$AG$8000,
MATCH(1,($D$1:$D$8000=Sheet1!D1)*($J$1:$J$8000=She et1!J1),0))

But the formula should be returning something from sheet1, column AG.

Maybe you want:

=INDEX(Sheet1!$AG$1:$AG$8000,
MATCH(1,(sheet1!$D$1:$D$8000=D1)*(sheet1!$J$1:$J$8 000=J1),0))

This formula is on sheet2???

Mrbanner wrote:

Thanks Formula searches the way I need
But for some reason it is not Inputting the Correct Information into AG

Cells
=INDEX(Sheet1!$AG$1:$AG$8000,MATCH(1,($D$1:$D$8000 =Sheet1!D1)*($J$1:$J$8000**=Sheet1!J1),0))

With the data I have it will not be in the same order all the time and
items and lines will be removed and added. I think this code is for if
the line stay the same

For example
On Sheet 1
D= Account Number
J= Product Code
AG= Notes

Sheet 2 is the same
D= Account Number
J= Product Code
AG= Notes

But the information inside the cells will mostly be different
And in different order.
What I needs that if Sheet1 (D & J) upto 8000 lines = (Sheet2 (D & J)

upto 8000 lines then Sheet 2 (AG) = Sheet 1 (AG)

Sorry hard to explain
In a nut shell
Sheet1 (D&J) LINE 5)
Sheet2 (D&J) LINE 800)
Both Match I have notes typed In AG(Sheet1)
Now I need these notes to be shown in Sheet2(line 800) now?

Reply

--

Dave Peterson

Try this in sheet2 A1

=INDEX(sheet1!AG1:AG8000,MATCH(D1&" "&J1,sheet1!$D$1:$D$8000&"
"&sheet1!$J$1:$J$8000,0))

Enter as an array formula by pressing CTL-Shift-Enter at the same time.
then drag/copy down to row 8000

HTH

"Mrbanner" wrote:

Thanks Formula searches the way I need
But for some reason it is not Inputting the Correct Information into AG



Cells
=INDEX(Sheet1!$AG$1:$AG$8000,MATCH(1,($D$1:$D$8000 =Sheet1!D1)*($J$1:$J$8000Â*Â*=Sheet1!J1),0))



With the data I have it will not be in the same order all the time and
items and lines will be removed and added. I think this code is for if
the line stay the same


For example
On Sheet 1
D= Account Number
J= Product Code
AG= Notes


Sheet 2 is the same
D= Account Number
J= Product Code
AG= Notes


But the information inside the cells will mostly be different
And in different order.
What I needs that if Sheet1 (D & J) upto 8000 lines = (Sheet2 (D & J)



upto 8000 lines then Sheet 2 (AG) = Sheet 1 (AG)


Sorry hard to explain
In a nut shell
Sheet1 (D&J) LINE 5)
Sheet2 (D&J) LINE 800)
Both Match I have notes typed In AG(Sheet1)
Now I need these notes to be shown in Sheet2(line 800) now?


Reply

Posted this response in your original thread in microsoft.public.excel.
---------
"Mrbanner" wrote:
....
Sheet1 (D&J) LINE 5)
Sheet2 (D&J) LINE 800)
Both Match I have notes typed In AG(Sheet1)
Now I need these notes to be shown in Sheet2(line 800) now?

Think this orientation should now be correct ..

In Sheet2,

Put in AG2, and array-enter:
=INDEX(Sheet1!$AG$2:$AG$8000,
MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8 000=J2),0))
Copy AG2 down

And perhaps better with error-traps to return blanks ("") for non-matching
lines, etc, we could put instead in AG2, array-enter, and fill down:

=IF(OR(D2="",J2=""),"",
IF(ISNA(MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$ J$2:$J$8000=J2),0)),"",
INDEX(Sheet1!$AG$2:$AG$8000,
MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8 000=J2),0))))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

You should probably ignore my previous post and use Dave's formula. In all
likelihood it is more efficient, and, given 8000 rows, you should use the
more efficient formula.

"DOR" wrote:

Try this in sheet2 A1

=INDEX(sheet1!AG1:AG8000,MATCH(D1&" "&J1,sheet1!$D$1:$D$8000&"
"&sheet1!$J$1:$J$8000,0))

Enter as an array formula by pressing CTL-Shift-Enter at the same time.
then drag/copy down to row 8000

HTH

"Mrbanner" wrote:

Thanks Formula searches the way I need
But for some reason it is not Inputting the Correct Information into AG



Cells
=INDEX(Sheet1!$AG$1:$AG$8000,MATCH(1,($D$1:$D$8000 =Sheet1!D1)*($J$1:$J$8000Â*Â*=Sheet1!J1),0))



With the data I have it will not be in the same order all the time and
items and lines will be removed and added. I think this code is for if
the line stay the same


For example
On Sheet 1
D= Account Number
J= Product Code
AG= Notes


Sheet 2 is the same
D= Account Number
J= Product Code
AG= Notes


But the information inside the cells will mostly be different
And in different order.
What I needs that if Sheet1 (D & J) upto 8000 lines = (Sheet2 (D & J)



upto 8000 lines then Sheet 2 (AG) = Sheet 1 (AG)


Sorry hard to explain
In a nut shell
Sheet1 (D&J) LINE 5)
Sheet2 (D&J) LINE 800)
Both Match I have notes typed In AG(Sheet1)
Now I need these notes to be shown in Sheet2(line 800) now?


Reply

Works great thanks
=IF(OR(D2="",J2=""),"",
IF(ISNA(MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$ J$2:$J$8000=J2),0)),"",

INDEX(Sheet1!$AG$2:$AG$8000,
MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8 000=J2),0))))

Works great thanks
=IF(OR(D2="",J2=""),"",
IF(ISNA(MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$ J$2:$J$8000=J2),0)),"",

INDEX(Sheet1!$AG$2:$AG$8000,
MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8 000=J2),0))))

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Mrbanner" wrote in message
oups.com...
Works great thanks
=IF(OR(D2="",J2=""),"",
IF(ISNA(MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$ J$2:$J$8000=J2),0)),"",

INDEX(Sheet1!$AG$2:$AG$8000,
MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8 000=J2),0))))