 # If a Looked Up MAX repeats, return the multiple values from corresponding rows

• I want to lookup the max value in Col A, and have the name/s, which are in Col B, returned in another part of the sheet.

I have attached a sample workbook.

How can I do this? Do I have the option to either have names in separate cells or concatenated?

Many TIAs! ## Files

• Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

Try

=INDEX(\$B\$1:\$B\$12,MATCH(MAX(\$A\$1:\$A\$12),\$A\$1:\$A\$12,0)) >>>for max Name

=INDEX(\$B\$1:\$B\$12,MATCH(Min(\$A\$1:\$A\$12),\$A\$1:\$A\$12,0)) >>>for min Name.

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

• Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

But this only returns one of the values in column B which has a MAX value in column A.

In the workbook, the max value in column A is 7, and this repeats in row 6 and 9, so I want the names in B6 and B9 to be returned.

TIA • Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

In this case we do this.

In C1 and copy down this formula.

=IF(A1=LARGE(\$A\$1:\$A\$12;1);1;IF(A1=SMALL(\$A\$1:\$A\$12;1);2;""))

This will be a helper and hidden column.

In F1, this ARRAY formula

=INDEX(\$B\$1:\$B\$12;SMALL(IF(\$C\$1:\$C\$12=1;ROW(\$B\$1:\$B\$12));ROW(B1))) >>>for max

=INDEX(\$B\$1:\$B\$12;SMALL(IF(\$C\$1:\$C\$12=1;ROW(\$B\$1:\$B\$12));ROW(B1)))>>>for min.

Does this works for you?

## Files

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

• Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

Thanks! This is returning all the names in Col B which have the maximum values in Col A and names in Col B which have the minimum value in Col A.:congrats:

Is there any way to replace the cells displaying #NUM! with " " ?

TIA!
Sykes

• Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

You are welcome! Thanks for the feedback.

Quote

...Is there any way to replace the cells displaying #NUM! with " " ?

=IFERROR(My formula),"")

=iferror(INDEX(\$B\$1:\$B\$12;SMALL(IF(\$C\$1:\$C\$12=1;ROW(\$B\$1:\$B\$12));ROW(B1)));"")

Change the semi colons to comma, if you have to do this.

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

• Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

I had to change the semi-colons to commas.. and it didn't work...

I get Arnold as my first name for highest score...

I'm trying, but not doing very well at figuring out how to get it to work...

:?

Update: Now I've tried applying the formula given in reply#4 but even that's not working... What significance do the curly brackets have?

• Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

Quote

....What significance do the curly brackets have?

Arrays formulas create these brackets(No need to type these) if you confirm(Control+Shift+Enter) correctly these. Read this excellent article about Arrays Formulas.

http://www.cpearson.com/excel/ArrayFormulas.aspx

In your case now. Do you use Excel < 2007?

If YES, then IFERROR will not work for you. You need IF(ISERROR function.

=IFERROR(INDEX(\$B\$1:\$B\$12;SMALL(IF(\$C\$1:\$C\$12=1;ROW(\$B\$1:\$B\$12));ROW(B1)));"")

=IF(ISERROR(INDEX(\$B\$1:\$B\$12;SMALL(IF(\$C\$1:\$C\$12=1;ROW(\$B\$1:\$B\$12));ROW(B6))));"";INDEX(\$B\$1:\$B\$12;SMALL(IF(\$C\$1:\$C\$12=1;ROW(\$B\$1:\$B\$12));ROW(B6))))

Take a look to the example sheet. I used both of these functions in my formula. If you use Excel<2007, maybe you will get some errors in some cells that i used IFERROR....

## Files

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

• Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

I wasn't aware of ctrl + shift + enter. Thanks for that And I have Office 2010.

But the formula doesn't seem to work when I try it myself... I've attached the workbook to show you that I'm applying the formula as you have written it - the INDEX nor the IFERROR seems to work for another example..

(Also, in place of ranges, can we use Names? I did assign some before but as it was working I thought we couldn't... But since the formulae aren't working for ranges either, I guess using Names in place of ranges would be fine..)

TIA!
Sykes

## Files

• Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

In F16 and copy down.

=IFERROR(INDEX(\$A\$16:\$A\$22;SMALL(IF(\$E\$16:\$E\$22=1;ROW(\$A\$16:\$A\$22)-15);ROW(A1)));"")

Pls see the red part of the formula.

Edit: YES. You can use Named ranges.

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

• Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

Great! Finally got it to work using your help (although I SWEAR I was doing the same thing over and over and it just decided to work the umpteenth time... :smile:)

So... I'm assuming the -15 was needed to specify the offset from Row 1, where Excel would start its search by default?

Getting there slowly... thanks Fotis1991 :smile:

• Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

You are welcome.

Quote

..So... I'm assuming the -15 was needed to specify the offset from Row 1, where Excel would start its search by default?

YES. Also the last A1 is for ..counting...it's become A2---A3....as you drag down.First, second, third.....n case.....

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!