Vlookup for max value without using an array

[njkollauf]

a	2
g	4
q	6
f	7
a	4
t	6

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

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:

KD1	KD2, KD4	1
KD2
KD3	KD2	2
KD4	KD3	6

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

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.

Row\Col	A​	B​	C​	D​	E​
1​	code	value			a
2​	a	2​			code
3​	g	4​			=a
4​	q	6​			4​
5​	f	7​
6​	a	4​
7​	t	6​

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>