Need help with VLOOKUP w/ RANK or MAX function

psychocandy

New Member
Joined
Jul 26, 2007
Messages
12
Hello All - I am hoping someone can come up with a solution for my current dilemma:

I am dumping data out of SAP, including the following fields:

* Vendor
* Material
* PO #
* Qty
* Amt
* Date

I would like to concatenate the material and vendor and pull in the PO # for the most recent date.

The VLOOKUP part is easy on the material/vendor, but I haven't had any luck in integrating a MAX, LARGE or RANK function to pull in the PO corresponding to the most recent date.

Once the PO # associated with the most current date can be identified for the material/vendor combination, I will need to utilize another vlookup to match the PO # up against a separate data file to pull in pricing. If anyone can think of a way to accomplish this in one step as opposed to multiple formulas please kindly advise.

If anyone can assist I would be most appreciative.

Thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
To find the PO # associated with the latest date where Vendor_field="Vendor1" and Material_Field="Material1" try

=INDEX(PO_Field,MATCH(1,(Vendor_Field="Vendor1")*(Material_Field="Material1")*(Date_Field=MAX(IF(Vendor_Field="Vendor1",IF(Material_Field="Material1",Date_Field)))),0))

confirmed with CTRL+SHIFT+ENTER
 

psychocandy

New Member
Joined
Jul 26, 2007
Messages
12
Thanks Barry, I am still trying to play with this to get it to work. Can you please reference the following section of the formula you provided and answer a quick question:

(Vendor_Field="Vendor1",...

Can "Vendor1" be a cell reference or does this have to be "hard-coded" in the formula itself? It would be easier to use the cell reference for programming purposes, but I am having trouble getting the formula to work.

I also realized that I may have to add PLANT as a fouth criteria but could easily accomplish this by extrapolating from the formula you provided.

Thanks again for your assistance
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Yes, you can certainly use cell references, see this example
Book1
ABCDEFGHI
1Vendor_FieldMaterial_FieldDate_FieldPO_Field
2as28-Jan-19851VendorMaterialLatest PO
3xy06-Aug-19902xy4
4yx01-Mar-19853ay5
5xy31-Jan-19964
6ay30-May-19975
7xy19-Feb-19836
8ce06-Sep-19967
9ay11-Jan-19928
1003-Nov-19849
11
Sheet2


formula in H3 references the vendor in F3 and Material in G3:

=INDEX(PO_Field,MATCH(1,(Vendor_Field=F3)*(Material_Field=G3)*(Date_Field=MAX(IF(Vendor_Field=F3,IF(Material_Field=G3,Date_Field)))),0))

confirmed with CTRL+SHIFT+ENTER and copied down the column

Vendor_Field etc. are named ranges
 

psychocandy

New Member
Joined
Jul 26, 2007
Messages
12

ADVERTISEMENT

Thanks Barry, that example was most helpful...

One last hurdle to overcome - The vendor, PO and material # are all pure # formats, not text.

Vendor = 6 digit #
Material = 8 digit #
PO # = 10 digit #

I am having a problem with #NUM! errors on the data ranges. This was determined by evaluating the formula.

I used the text to columns function to eliminate any text qualifiers but I still can't seem to get the formula to pull in the data in number format.

Do you have any suggestions for an easy fix here? I appreciate your support.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
The suggested formula should work whether your data is text or numeric.

How are your named ranges defined? If they are entire columns then you might get #NUM! choose a fixed range e.g. A2:A1000 and make sure all ranges are the same size
 

psychocandy

New Member
Joined
Jul 26, 2007
Messages
12
Barry, thanks this fixed the problem. I had not used named ranges but rather referenced the entire column (i.e A:A).

The formula works and will be a tremendous help in setting up this PPV model. Many thanks for your asssitance!

My last issue is eliminating the #N/A! error for situations where no match is found.

Would you recommend a nested ISNA function for this (the formula then becomes quite long), or is there a more efficient path that can be taken?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Try

=IF(SUM((Vendor_Field=F3)*(Material_Field=G3)),INDEX(PO_Field,MATCH(1,(Vendor_Field=F3)*(Material_Field=G3)*(Date_Field=MAX(IF(Vendor_Field=F3,IF(Material_Field=G3,Date_Field)))),0)),"")

This should give a blank if you don't have a matching row
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top