Reference/Lookup question

Q

quick_question

New Member
Joined
May 31, 2011
Messages
32
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hi,

Assume in cell E1 is your criteria for material eg:1234 then in E2 place the following formula:

=INDEX($C$2:$C$16,MATCH(MAX(IF($A$2:$A$16=$E$1,$B$2:$B$16,0)),$B$2:$B$16,0))

Its an array formula so it has to be comfirmed with Ctl+Shift+Enter
Data ranges from row 2 to row 16 in this example. Row 1 is for the headers

George


quick_question said:
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.
Click to expand...
 
Upvote 0
quick_question said:
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.
Click to expand...
If different Material# might be assciated with identical PO#'s...

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>Material#</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>PO#</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Price</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>1234</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>1234</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>5678</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>5378</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>3892</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>1234</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

E1: 1234

E2, control+shift+enter, not just enter:
Code: 
=INDEX($C$2:$C$6,MATCH(1,IF($A$2:$A$6=E1,
    IF($B$2:$B$6=MAX(IF($A$2:$A$6=E1,$B$2:$B$6)),1)),0))
 
Upvote 0
quick_question said:
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.
Click to expand...
Maybe this...

Book1
ABCDEF
1MaterialPOPrice_LookupPrice
2123424_12341
3567835___
4537853___
5389282___
6123441___
Sheet1

Array formula** entered in F2:

=MAX(IF(A2:A6=E2,IF(B2:B6=MAX(IF(A2:A6=E2,B2:B6)),C2:C6)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes you won't have duplicate PO's for a material. If there are, then you haven't told us what result you'd want in that case.
 
Upvote 0
Hi Aladin,

Could you please explain the logic behind using Match(1...) in the row argument of the INDEX function?

Thanks

George

Aladin Akyurek said:
If different Material# might be assciated with identical PO#'s...

<table style="width: 240pt; border-collapse: collapse;" width="320" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 48pt;" width="64" span="5"></colgroup><tbody><tr style="height: 14.4pt;" height="19"><td style="border: 0.5pt solid windowtext; background-color: transparent; width: 48pt; height: 14.4pt;" class="xl63" width="64" height="19">Material#</td><td style="border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; background-color: transparent; width: 48pt;" class="xl63" width="64">PO#</td><td style="border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; background-color: transparent; width: 48pt;" class="xl63" width="64">Price</td><td style="border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; background-color: transparent; width: 48pt;" class="xl63" width="64">
</td><td style="border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; background-color: transparent; width: 48pt;" class="xl63" width="64" align="right">1234</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl63" align="right" height="19">1234</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63" align="right">2</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63" align="right">4</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63">
</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63" align="right">1</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl63" align="right" height="19">5678</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63" align="right">3</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63" align="right">5</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63">
</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63">
</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl63" align="right" height="19">5378</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63" align="right">5</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63" align="right">3</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63">
</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63">
</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl63" align="right" height="19">3892</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63" align="right">8</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63" align="right">2</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63">
</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63">
</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl63" align="right" height="19">1234</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63" align="right">4</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63" align="right">1</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63">
</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl63">
</td></tr></tbody></table>

E1: 1234

E2, control+shift+enter, not just enter:
Code: 
=INDEX($C$2:$C$6,MATCH(1,IF($A$2:$A$6=E1,
    IF($B$2:$B$6=MAX(IF($A$2:$A$6=E1,$B$2:$B$6)),1)),0))
Click to expand...
 
Upvote 0
GK039 said:
Hi Aladin,

Could you please explain the logic behind using Match(1...) in the row argument of the INDEX function?

Thanks

George
Click to expand...

Hi George,

Forcing multiple conditions to evaluate to a certain item i allows us invoking
MATCH with i as look up value while match-type is set to 0. It's almost a custom to have i = 1. INDEX is then fed with the position result MATCH determines.

Hope this helps.
 
Upvote 0
Hi Aladin,

Thank you very much for the clarification. So, if i got it right the value_if_true part of the IF statement evaluates to 1, which is then the lookup_value of the MATCH function?

George

Aladin Akyurek said:
Hi George,

Forcing multiple conditions to evaluate to a certain item i allows us invoking
MATCH with i as look up value while match-type is set to 0. It's almost a custom to have i = 1. INDEX is then fed with the position result MATCH determines.

Hope this helps.
Click to expand...
 
Upvote 0
GK039 said:
Hi Aladin,

Thank you very much for the clarification. So, if i got it right the value_if_true part of the IF statement evaluates to 1, which is then the lookup_value of the MATCH function?

George
Click to expand...

Yes. IF(s) return(s) an array (a vector) of 1's and FALSE's against which 1 is matched.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Extracting a specific number of characters from a multiline cell
Replies
7
Views
368
etaf
etaf
C
  • Question Question
ComboBox and TextBox
Replies
0
Views
345
Cr4ZyPP
C
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
419
STARL0RD
S
S
  • Solved
Concatenate Numerous rows based off cells value
Replies
4
Views
336
hagia_sofia
hagia_sofia
M
  • Question Question
Trouble with a formula returning duplicate values
2
Replies
19
Views
995
Cubist
Cubist

Forum statistics

Threads
1,224,586
Messages
6,179,729
Members
452,939
Latest member
WCrawford

Share this page

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