Formula for MAX value and lookup query?

deacon10 · May 8, 2012

Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
Can any of you excel genius’s help? I have been struggling with what seemed at first like a very simple formula. I have the sample data as shown below and I am trying to work out a formula that provides the latest value in sheet 2 from sheet 1 column AF based on a number of conditions.<o

></o

>
<o

></o

>
If Sheet2 A1:A500 = Sheet1 F1:F500<o

></o

>
And Sheet1 G1:G500 = max & Sheet1 AF1:AF500 doesn’t = blank (i.e. last value in column AF)<o

></o

>
Then return adjacent value from Sheet1 AF1:AF500<o

></o

>
<o

></o

>
<o

></o

>
Sheet 1<o></o>
F-----G-----AF<o></o>
127--1-----15.6<o

></o

>
127--2------3.1<o

></o

>
128--1------2.0 <o

></o

>
128--2-----<o

></o

>
129--1------2.5<o

></o

>
129--2------1.6<o

></o

>
129--3------2.7<o

></o

>
130--1------3.3<o

></o

>
130--2------3.1<o

></o

>
130--3------1.6<o

></o

>
130--4------5.4<o

></o

>
130--5------<o

></o

>
130--6------

Sheet 2<o></o>
A--------B (desired results)<o></o>
127-----3.1<o

></o

>
128-----2.0<o

></o

>
129-----2.7<o

></o

>
130-----5.4
<o

></o

>
<o

></o

>
<o

></o

>
Does anybody have any suggestions?<o

></o

>
<o

></o

>
Many many eternal thanks for any help<o

></o

>

Aladin Akyurek · May 8, 2012

deacon10 said:
Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Can any of you excel genius’s help? I have been struggling with what seemed at first like a very simple formula. I have the sample data as shown below and I am trying to work out a formula that provides the latest value in sheet 2 from sheet 1 column AF based on a number of conditions.<o></o>
<o></o>
If Sheet2 A1:A500 = Sheet1 F1:F500<o></o>
And Sheet1 G1:G500 = max & Sheet1 AF1:AF500 doesn’t = blank (i.e. last value in column AF)<o></o>
Then return adjacent value from Sheet1 AF1:AF500<o></o>
<o></o>
<o></o>
Sheet 1<o></o>
F-----G-----AF<o></o>
127--1-----15.6<o></o>
127--2------3.1<o></o>
128--1------2.0 <o></o>
128--2-----<o></o>
129--1------2.5<o></o>
129--2------1.6<o></o>
129--3------2.7<o></o>
130--1------3.3<o></o>
130--2------3.1<o></o>
130--3------1.6<o></o>
130--4------5.4<o></o>
130--5------<o></o>
130--6------

Sheet 2<o></o>
A--------B (desired results)<o></o>
127-----3.1<o></o>
128-----2.0<o></o>
129-----2.7<o></o>
130-----5.4
<o></o>
<o></o>
<o></o>
Does anybody have any suggestions?<o></o>
<o></o>
Many many eternal thanks for any help<o></o>

Sheet2

B1, control+shift+enter, not just enter, and copy down:

Rich (BB code):

=INDEX(Sheet1!$AF$1:$AF$500,MIN(IF(Sheet1!$F$1:$F$500=A1,
  IF(Sheet1!$G$1:$G$500=MAX(IF(Sheet1!$F$1:$F$500=A1,
   IF(ISNUMBER(Sheet1!$AF$1:$AF$500),Sheet1!$G$1:$G$500))),
    ROW(Sheet1!$AF$1:$AF$500)-ROW(Sheet1!$AF$1)+1))))

njimack · May 8, 2012

Confirm with ctrl+shift+enter:
=SUM(IF(Sheet1!F$2:F$500=A2,IF(Sheet1!G$2:G$500=MAX(IF(Sheet1!F$2:F$500=A2,IF(Sheet1!AF$2:AF$500<>"",Sheet1!G$2:G$500))),Sheet1!AF$2:AF$500)))

deacon10 · May 8, 2012

Hi all, many thanks for the formulas.

They both appear to work well except for one strange quirk....

Sheet 1<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
F-----G-----AF<o></o>
127--1-----15.6<o

></o

>
127--2------3.1<o

></o

>
128--1------2.0 <o

></o

>
128--2------5.5<o></o>
129--1------2.5<o

></o

>
129--2------1.6<o

></o

>
129--3------2.7<o

></o

>
130--1------3.3<o

></o

>
130--2------3.1<o

></o

>
130--3------1.6<o

></o

>
130--4------5.4<o

></o

>
130--5------<o

></o

>
130--6------

If i were to enter a new value under Column AF (see highlighted example), i would still get the old value 2.0 and not the new value 5.5. Is this something i am doing wrong? My calculation settings are set to automotic and i have tried re-running the formula.

Cheers again

njimack · May 8, 2012

Both formulae return 5.5 for me. Are you sure your value in AF doesn't contain any leading/trailing spaces?

EDIT: Check that all 3 cells in that row don't contain leading/trailing spaces.

Aladin Akyurek · May 8, 2012

deacon10 said:
Hi all, many thanks for the formulas.

They both appear to work well except for one strange quirk....

Sheet 1<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
F-----G-----AF<o></o>
127--1-----15.6<o></o>
127--2------3.1<o></o>
128--1------2.0 <o></o>
128--2------5.5<o></o>
129--1------2.5<o></o>
129--2------1.6<o></o>
129--3------2.7<o></o>
130--1------3.3<o></o>
130--2------3.1<o></o>
130--3------1.6<o></o>
130--4------5.4<o></o>
130--5------<o></o>
130--6------

If i were to enter a new value under Column AF (see highlighted example), i would still get the old value 2.0 and not the new value 5.5. Is this something i am doing wrong? My calculation settings are set to automotic and i have tried re-running the formula.

Cheers again

I'm still assuming that you don't want to sum multiple records associated with a max value...

Control+sshift+enter, not just enter:

Rich (BB code):

=INDEX(Sheet1!$AF$1:$AF$500,MIN(IF(Sheet1!$F$1:$F$500=A1,
  IF(ISNUMBER(Sheet1!$AF$1:$AF$500),
  IF(Sheet1!$G$1:$G$500=MAX(IF(Sheet1!$F$1:$F$500=A1,
  IF(ISNUMBER(Sheet1!$AF$1:$AF$500),Sheet1!$G$1:$G$500))),
   ROW(Sheet1!$AF$1:$AF$500)-ROW(Sheet1!$AF$1)+1)))))

deacon10 · May 8, 2012

njimack said:
Both formulae return 5.5 for me. Are you sure your value in AF doesn't contain any leading/trailing spaces?

EDIT: Check that all 3 cells in that row don't contain leading/trailing spaces.

Hi again all,

Just to let you know, both formulas work like an absolute dream. I think the above issue i encountered was the time it was taking to re-calculate the formulas (because in my original sheet the range goes into the 1000's).

I cant thank you both enough for this,

Many many thanks,

C

Aladin Akyurek · May 8, 2012

deacon10 said:
Hi again all,

Just to let you know, both formulas work like an absolute dream. I think the above issue i encountered was the time it was taking to re-calculate the formulas (because in my original sheet the range goes into the 1000's).

I cant thank you both enough for this,

Many many thanks,

C

Add the following record to the sample you posted: You should get different results...

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 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=xl65 height=19 width=64 align=right>128</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=xl66 width=64 align=right>2</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=xl66 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=xl66 width=64 align=right>7.5</TD></TR></TBODY></TABLE>

njimack · May 8, 2012

Aladin Akyurek said:
Add the following record to the sample you posted: You should get different results...

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" align=right width=64 height=19>128</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64>2</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64>7.5</TD></TR></TBODY></TABLE>

But in the sample the OP posted, each combination of columns F and G appear only once.

Formula for MAX value and lookup query?

deacon10

Board Regular

Excel Facts

Aladin Akyurek

MrExcel MVP

njimack

Well-known Member

deacon10

Board Regular

njimack

Well-known Member

Aladin Akyurek

MrExcel MVP

deacon10

Board Regular

Aladin Akyurek

MrExcel MVP

njimack

Well-known Member

Similar threads

Forum statistics

Share this page

Formula for MAX value and lookup query?

Board Regular

Excel Facts

MrExcel MVP

Well-known Member

Board Regular

Well-known Member

MrExcel MVP

Board Regular

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock