I am trying to return a value based on the functions =match() and =index()

**mboymosweu** · 11-14-2019, 03:16 AM

Hello

I need assisttance i want excel to return the name associated with highest number meeting the condition that is greater than 0% and less than or equal to 20%. In the excel table attached the name i wish to be returned is Judith since its 19% the highest in the list but not over the target which is 20%.

Find attached.

**Pepe Le Mokko** · 11-14-2019, 03:18 AM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

Note:I did it for you this time only using one of your own sentences

**Pete_UK** · 11-14-2019, 09:24 AM

You can use this array* formula, say in cell D2:

=INDEX(A2:A10,MATCH(MAX(IF(($B$2:$B$10 <= $B$1)*($B$2:$B$10 > 0),$B$2:$B$10)),$B$2:$B$10,0))

* Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual Enter.

Hope this helps.

Pete

**mboymosweu** · 11-14-2019, 10:13 AM

Hi Pete

Excel should return the maximum number which is greater than 0 but less than 20. which means 26, 30, 32, 16, 0,48,14 and 21 are out but the number which satisfied the query is 19 because its the heighest between the range 0-20. Hence the name that must be returned is Judith. Hope it makes sense.

**Fluff13** · 11-14-2019, 10:31 AM

Pete's formula works for me, did you confirm it with Ctrl Shift Enter?

Here's another formula that does not need the CSE
=INDEX(A2:A10,AGGREGATE(15,6,(ROW(A2:A10)-ROW(A2)+1)/((B2:B10<=B1)*(B2:B10>0)),1))

I am trying to return a value based on the functions =match() and =index()

LinkBack

Thread Tools

Rate This Thread

Display

I am trying to return a value based on the functions =match() and =index()

Re: return the name associated with highest number meeting the conditions

Re: return the name associated with highest number meeting the conditions

Re: I am trying to return a value based on the functions =match() and =index()

Re: I am trying to return a value based on the functions =match() and =index()

Thread Information

Users Browsing this Thread

Similar Threads

data set too big for use of INDEX fuction

Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail

INDEX Fuction - Urgent Help Needed

Excel Index Function - Matching calendar dates and activities using an index fuction

IF fuction &Index

Index & Match Fuction

[SOLVED] Complicated lookup/match fuction

Bookmarks

Bookmarks

Posting Permissions