VBA/Vlookup/Idex match

[Confuze]

I have a workbook with two worksheet. Please help me out with anything Vlookup/Index match, VBA etc..

On sheet1 I have column A= item number, column G=price and column M=location. On sheet2 I have column A= item number, column D=-lot, column G=price and column M=location.

But on sheet2 on Column A has same item number with multiple rows with different lot on column D. is there a way that can be done in VBA/Vlookup anything that look up the oldest lot and copy price and location to sheet1? Help me out please. I spent hours pretty much of my work day search each item copy and paste. Attachment is an example



Thanks in advance.

[mehmetcik]

LOL.

You sound desperate.

So I will look at this for you.

Ok

Paste this formula into cell B2 using Ctrl Shift Enter. you should get the result 343

=LARGE(IF(A3=Sheet2!A1:A20000,ROW(A1:A20000),0)*IF(Sheet2!J1:J20000>1,1,0),1)

This is the largest row in Column J that has the same part number and at least one item ready to ship.


This formula is slightly differrent and returns the largest row with at least the number of items as on the order
=LARGE(IF(A3=Sheet2!A1:A20000,ROW(A1:A20000),0)*IF(Sheet2!J1:J20000>=D3,1,0),1)


Once you have the row number it is simple to use the index function to return the appropriate dats

Eg to return the sale price

=INDEX(Sheet2!M1:M2000,B2,1)


or

Entered using Ctrl Shift Enter.
=INDEX(Sheet2!M1:M2000,LARGE(IF(A3=Sheet2!A1:A20000,ROW(A1:A20000),0)*IF(Sheet2!J1:J20000>1,1,0),1),1)

[Confuze]

LOL.

You sound desperate.

So I will look at this for you.

Ok

Paste this formula into cell B2 using Ctrl Shift Enter. you should get the result 343

=LARGE(IF(A3=Sheet2!A1:A20000,ROW(A1:A20000),0)*IF(Sheet2!J1:J20000>1,1,0),1)

This is the largest row in Column J that has the same part number and at least one item ready to ship.


This formula is slightly differrent and returns the largest row with at least the number of items as on the order
=LARGE(IF(A3=Sheet2!A1:A20000,ROW(A1:A20000),0)*IF(Sheet2!J1:J20000>=D3,1,0),1)


Once you have the row number it is simple to use the index function to return the appropriate dats

Eg to return the sale price

=INDEX(Sheet2!M1:M2000,B2,1)


or

Entered using Ctrl Shift Enter.
=INDEX(Sheet2!M1:M2000,LARGE(IF(A3=Sheet2!A1:A20000,ROW(A1:A20000),0)*IF(Sheet2!J1:J20000>1,1,0),1),1)

Yes, I'm desperate in need of help. Can you please paste the formula on the file for me? I tried pasting on B2 nothing happen.


Thanks

[Gregb11]

If you re-read mehmetcik's response, you'll note the first formula is an "Array formula". This means that when you enter that formula, DO NOT just hit enter key, but hit the Shift, Ctrl, and Enter keys at the same time.

[mehmetcik]

Columns B and H ammened

[Pepe Le Mokko]

éConfuze
Administrative Note:

Please don't quote entire posts unnecessarily. They are just clutter and make threads hard to read.
Use the "Quick reply" instead
Thanks