vlookup w/ multiple values, returning max

[fotoman]

Ok, I've scoured the search results and haven't quite run across what I'm looking to do

I have a long list of entries: names & numbers. There will be multiple entries for each name in the list, but I only want to return the max value for one that matches.

A 3
B 6
C 8
D 4
A 6
D 1

I need to lookup the first column, and return the max value associated with it. Neither column is sorted, and I will be looking up quite a lot of items in column 'A'. Oh, sorting the results is not really an option.

So, if I lookup 'A' it should return 6.

Thanks

 

[jasonb75]

Welcome to the board!

Maybe something on the lines of

Excel Workbook
	A	B	C	D	E
1	A	3
2	B	6		A	6
3	C	8		B	6
4	D	4		C	8
5	A	6		D	4
6	D	1
Sheet

will help?

 

[rforrest]

Thank you so much, jasonb75. I had been wrestling with this for a very long time! Saved me a ton of work. You rock!

Welcome to the board!

Maybe something on the lines of

*	A	B	C	D	E
A	*	*	*
B	*	A
C	*	B
D	*	C
A	*	D
D	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:16px;"><col style="width:14px;"><col style="width:64px;"><col style="width:16px;"><col style="width:14px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 {=MAX(IF($A$1:$A$6=D2,$B$1:$B$6))} E3 {=MAX(IF($A$1:$A$6=D3,$B$1:$B$6))} E4 {=MAX(IF($A$1:$A$6=D4,$B$1:$B$6))} E5 {=MAX(IF($A$1:$A$6=D5,$B$1:$B$6))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

will help?