Reference/Lookup question

[quick_question]

Is there a formula that will reference a lookup, compare 2 numbers, and return the referenced column of the highest number.

ie

I want to look up the most recent price of a particular material.

I would like to enter a material # in say E2, and have it return the most recent price for material # 1234.

Is there a formula that would find material 1234, compare all PO#'s (in this case 2&4) and return the price for the highest PO# (here it would be 1)?

--A-------------B-------C
1-Material #----PO#----Price
2-1234----------2-------4
3-5678----------3-------5
4-5378----------5-------3
5-3892----------8-------2
6-1234----------4-------1

Thanks for your help.

 

[T. Valko]

I have used the formula provided and it works great, Thanks again all.

As always, there's a need for an iteration to this formula.

Can I compare the PO #s and return the price of the largest one, but this time use 2 criteria.

So before, we had this, where E1 was my criteria, and E2 was what the solution would return:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: rgb(255,255,255)" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 37px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center"><TD>

</TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-TOP: rgb(0,0,0) 1px solid; BORDER-LEFT: rgb(0,0,0) 1px solid; BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">Material #</TD><TD style="BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-TOP: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">PO#</TD><TD style="BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-TOP: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">Price</TD><TD style="BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-TOP: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); COLOR: rgb(255,255,255); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-TOP: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">1234</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT: rgb(0,0,0) 1px solid; BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; BACKGROUND-COLOR: rgb(204,255,204); TEXT-ALIGN: center">1234</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; BACKGROUND-COLOR: rgb(204,255,204); TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; BACKGROUND-COLOR: rgb(204,255,204); TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); COLOR: rgb(255,255,255); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT: rgb(0,0,0) 1px solid; BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">5678</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); COLOR: rgb(255,255,255); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); COLOR: rgb(255,255,255); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT: rgb(0,0,0) 1px solid; BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">5378</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); COLOR: rgb(255,255,255); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); COLOR: rgb(255,255,255); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT: rgb(0,0,0) 1px solid; BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">3892</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); COLOR: rgb(255,255,255); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); COLOR: rgb(255,255,255); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT: rgb(0,0,0) 1px solid; BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">1234</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); COLOR: rgb(255,255,255); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: rgb(0,0,0) 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: rgb(0,0,0); COLOR: rgb(255,255,255); BORDER-TOP-COLOR: rgb(0,0,0); BORDER-BOTTOM: rgb(0,0,0) 1px solid; TEXT-ALIGN: center">_</TD></TR></TBODY></TABLE>
Current formula for 1 criteria:
=INDEX(C2:C10,MATCH(MAX(IF(A2:A10=E1,B2:B10,0)),IF(A2:A10=E1,B2:B10),0))
Enclosed by Control, Shift, & Enter

This time, imagine the columns shift to the right; column A now has Vendor #'s just as material....column B is Material #, C is PO# and etc.

I'm trying to do the same thing as before, just add an additional criteria.

Thanks again for everyone's help...Much Appreciated!

You just need to add the conditon like this:

Still array entered.

=INDEX(C2:C10,MATCH(MAX(IF(A2:A10=E1,IF(Condition2,B2:B10))),IF(A2:A10=E1,IF(Condition,B2:B10)),0))