# Need help with VLOOKUP w/ RANK or MAX function

#### psychocandy

##### New Member
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
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
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
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

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

Replies
1
Views
481
Replies
1
Views
403
Replies
2
Views
274
Replies
2
Views
520
Replies
6
Views
305

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

### 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