Match help

M

Mrbanner

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
 
D

Dave Peterson

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=Sheet1!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$8000=J1),0))

This formula is on sheet2???
 
D

DOR

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
 
M

Max

Posted this response in your original thread in microsoft.public.excel.
---------
:
....
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?
Click to expand...

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$8000=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$8000=J2),0))))
 
D

DOR

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.
 
M

Mrbanner

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$8000=J2),0))))
 
M

Mrbanner

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$8000=J2),0))))
 
Top