Need help with VLOOKUP w/ RANK or MAX function

[psychocandy]

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

 

[barry houdini]

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]

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]

Yes, you can certainly use cell references, see this example

Book1
	A	B	C	D	E	F	G	H	I
1	Vendor_Field	Material_Field	Date_Field	PO_Field
2	a	s	28-Jan-1985	1		Vendor	Material	Latest PO
3	x	y	06-Aug-1990	2		x	y	4
4	y	x	01-Mar-1985	3		a	y	5
5	x	y	31-Jan-1996	4
6	a	y	30-May-1997	5
7	x	y	19-Feb-1983	6
8	c	e	06-Sep-1996	7
9	a	y	11-Jan-1992	8
10			03-Nov-1984	9
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]

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]

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]

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]

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