Vlookup for max value without using an array

[njkollauf]

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl136, width: 64"]a[/TD]
[TD="class: xl136, width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl136"]g[/TD]
[TD="class: xl136, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl136"]q[/TD]
[TD="class: xl136, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl136"]f[/TD]
[TD="class: xl136, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl136"]a[/TD]
[TD="class: xl136, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl136"]t[/TD]
[TD="class: xl136, align: right"]6
[/TD]
[/TR]
</tbody>[/TABLE]

From the data set above I would want to vlookup for "a" and return the value 4 instead of 2. I believe this post is a good starting point (http://www.mrexcel.com/forum/excel-questions/615464-find-max-value-1-criteria-no-array.html) the only issue is that I think it relies on the identifier to be a value. I also want to make the formula dynamic to include the whole column and not slow down the workbook processing speed which is why I want to stay away from arrays.

 

[MarcelBeug]

Assuming your data is in columns A and B and provided you have no gaps in your data in column A:

=LOOKUP(2,1/(MATCH($A$1:INDEX(A:A,COUNTA(A:A)),$A$1:INDEX(A:A,COUNTA(A:A)),0)=MATCH($D$1,$A$1:INDEX(A:A,COUNTA(A:A)),0)),$B$1:INDEX(B:B,COUNTA(A:A)))

 

[MarcelBeug]

Correction. The formula in post #2 would return the last value rather than the max value.

This should be better ($D$1 contains the search value "a"), provided values are > 0:

=MAX(INDEX(($A$1:INDEX($A:$A,COUNTA($A:$A))=$D$1)*$B$1:INDEX($B:$B,COUNTA($A:$A)),0))

 

[njkollauf]

That formula didn't work as it includes $d$1 which is not a value I provided.

I thought that this elegant solution of =LOOKUP(2^15,SEARCH(a$2:a$10,A2),b$2:b$10) that came from http://www.mrexcel.com/pc18.shtml would suffice but there is an issue.

To get into more detail I have this information:

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl138, width: 64"]KD1[/TD]
[TD="class: xl138, width: 64"]KD2, KD4[/TD]
[TD="class: xl138, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl138"]KD2[/TD]
[TD="class: xl138"][/TD]
[TD="class: xl138"][/TD]
[/TR]
[TR]
[TD="class: xl138"]KD3[/TD]
[TD="class: xl138"]KD2[/TD]
[TD="class: xl138, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl138"]KD4[/TD]
[TD="class: xl138"]KD3[/TD]
[TD="class: xl138, align: right"]6
[/TD]
[/TR]
</tbody>[/TABLE]

What I want to do is lookup in column B for the value in column A and return the largest value in column C. So in column D (where this formula should go), when I come to record "KD2", I would want a value of "2" to be returned.

 

[Aladin Akyurek]

Buy 2016. That seems to have a MAXIFS function, which you can invoke.

Otherwise, try DMAX, a database function.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]code[/td][td]value[/td][td][/td][td][/td][td]a[/td][/tr]


[tr][td]

2​

[/td][td] a[/td][td]

2​

[/td][td][/td][td][/td][td]code[/td][/tr]


[tr][td]

3​

[/td][td] g[/td][td]

4​

[/td][td][/td][td][/td][td]=a[/td][/tr]


[tr][td]

4​

[/td][td] q[/td][td]

6​

[/td][td][/td][td][/td][td]

4​

[/td][/tr]


[tr][td]

5​

[/td][td] f[/td][td]

7​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td] a[/td][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td] t[/td][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

E1: a (a code of interest)

In E2 just enter:

="="&E1

In E3 just enter:

<strike></strike>=DMAX(A1:B7,2,E2:E3)<strike></strike>