Index and Match multiple rows - only returning top row

**gwoolley0302** · 05-20-2024, 07:34 AM

Hi there,

I'm using an Index and Match function to search a dataset and return various values based upon one horizontal value (i.e. column B), and one vertical value (row 2). [Very simplified version of data is shown in a picture below]

The unique ID (i.e. column B) in some cases appears more than once in the dataset (highlighted in pink) and therefore has multiple rows in the dataset.

When requesting a value for an ID with multiple rows, it only returns the value found in the first relevant row - in the example shown a "no" - see orange highlighted cells.

However, what I need to do sometimes is overwrite that "no" with a "yes" if a yes is present in one of the other rows.

Any ideas of how to do this most easily? Thanks for your help.

Picture1.jpg

**AliGW** · 05-20-2024, 07:41 AM

Are you still using Excel 2019?

**Glenn Kennedy** · 05-20-2024, 08:16 AM

See if you can adapt this (I made a simpler sample, as we can NOT edit pictures of Excel files).

=IF(ISNUMBER(MATCH(D4,A:A,0)),IF(SUM(--ISNUMBER(SEARCH("Yes",INDEX(B:B,AGGREGATE(15,6,ROW($A$4:$A$15)/($A$4:$A$15=D4),ROW(INDIRECT("1:"&COUNTIF(A:A,D4))))))))=1,"Yes","No"),"Not Present")

**gwoolley0302** · 05-20-2024, 08:27 AM

Hi @AliGW - I'm using Office 365, so don't think I'd be limited to Office 2019.

**gwoolley0302** · 05-20-2024, 08:34 AM

Hi Glenn,

Sorry rookie error, attaching a file this time. I did try to adapt the formula you kindly shared, but it wasn't working for me.

Thanks for your help.

Greg

**AliGW** · 05-20-2024, 10:46 AM

Please update your forum profile to Office 365 without delay. Thanks.

**AliGW** · 05-20-2024, 10:48 AM

Clear ALL expected results, then in G4:

=BYROW(F4:F12,LAMBDA(r,XLOOKUP(r,B4:B14,D4:D14)))

**AliGW** · 05-20-2024, 10:51 AM

Or maybe this is what you want (returns the LAST entry for each 'person'):

=BYROW(F4:F12,LAMBDA(r,TAKE(FILTER(D4:D14,B4:B14=r),-1)))

**gwoolley0302** · 05-20-2024, 11:28 AM

Thank you@AliGW.

Profiled now updated.

The first solution isn't working for what I need sorry. For example in cell G5, that should be a Yes - as I'd want a 'Yes', if present in any row, to overwrite any other No's.

The second solution - 'last entry' - wouldn't work for the larger 'real life' dataset as in some cases there might be three or more rows with the same unique ID.

Any other thoughts - thank you!

**Glenn Kennedy** · 05-20-2024, 11:30 AM

Delete expected results... this returns the unique VALUES, and YES if YES is one of the possible answers.

=LET(A,B4:B14,B,D4:D14,C,UNIQUE(A),D,BYROW(C,LAMBDA(x,IF(ISERROR(MATCH("Yes",FILTER(B,A=x),0)),"No","Yes"))),HSTACK(C,D))

**AliGW** · 05-20-2024, 11:30 AM

So what needs to happen exactly for any unique ID? If there is ANY yes, return yes, and if not, return no - is that it?

**Glenn Kennedy** · 05-20-2024, 11:32 AM

Ali, that's my understanding of it.

**AliGW** · 05-20-2024, 11:36 AM

Yes - I'm not bothering, since I saw your suggestion.

**gwoolley0302** · 05-20-2024, 11:47 AM

Thanks both for your time.

Yep, that is the right understanding.

The formula =LET(A,B4:B14,B,D4:D14,C,UNIQUE(A),D,BYROW(C,LAMBDA(x,IF(ISERROR(MATCH("Yes",FILTER(B,A=x),0)),"No","Yes"))),HSTACK(C,D)) does the job - but no idea how it works! haha.

Probably a stupid question but I can't seem to 'select' the formula to try and adapt it for other files (?) I think I'm being stupid though.

**Glenn Kennedy** · 05-20-2024, 11:50 AM

I'm not really sure what you mean by "Select"... but if the setup is the same th eonly thing you need to change are the bits in RED:

=LET(A,B4:B14,B,D4:D14,C,UNIQUE(A),D,BYROW(C,LAMBDA(x,IF(ISERROR(MATCH("Yes",FILTER(B,A=x),0)),"No","Yes"))),HSTACK(C,D))

Excel does the rest. If that's the case...You're welcome. Thanks for letting us know that you got an answer.

However, if the setup is different, post another sample file.

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

**Glenn Kennedy** · 05-20-2024, 11:55 AM

In case you're interested (but I wouldn't use it, as the O365 friendly version at Post 15 will be MUCH faster), this is how my original suggestion should have looked in your sample file.

**gwoolley0302** · 05-20-2024, 12:13 PM

Thanks Glenn. Yep happy to use the suggestion at Post 15.

Just struggling to make the formula work to repeat the same task but for columns J and K in the sample file. I would just want column I, J and K all to refer to column H as opposed to having a repeated column H each time the formula is used. So wasn't sure which part to remove.

**Glenn Kennedy** · 05-20-2024, 12:31 PM

I'm not surprised that you got in a mess. So did I. I'd recommend a simpler approach:

=UNIQUE(B4:B14)

and this, copied ACROSS

BYROW($H4#,LAMBDA(x,IF(ISERROR(MATCH("Yes",FILTER(D4:D14,$B$4:$B$14=x),0)),"No","Yes")))

**gwoolley0302** · 05-23-2024, 06:41 AM

Sorry for the delayed reply Glenn.

That works perfectly, thanks very much for your time and solution!

**AliGW** · 05-23-2024, 07:13 AM

Here it is all in one:

Please Login or Register  to view this content.

Index and Match multiple rows - only returning top row

LinkBack

Thread Tools

Rate This Thread

Display

Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Re: Index and Match multiple rows - only returning top row

Thread Information

Users Browsing this Thread

Similar Threads

Textjoin & index match returning multiple rows

[SOLVED] Returning Multiple Rows of Data (VLOOKUP/INDEX MATCH)

Returning multiple columns with INDEX/MATCH?

[SOLVED] Two criteria Index Match on multiple sheets returning multiple values

[SOLVED] Index match with multiple criteria and returning last value

Returning multiple entries with INDEX and MATCH

Returning MULTIPLE values with Index and Match

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions