INDEX MATCH SMALL but ignore duplicates

**pareid** · 07-11-2019, 03:39 PM

Hi,

I am trying to lookup one of two values from Sheet2 cells M2 or N2 in column C of sheet1 and then return what is in column A. My issue is that column A will have duplicates and I only one to bring through unique values. I have the following formula that seems to work but you will see on Sheet2 of the attached example it brings through duplicates in columns B to G of Sheet2.

=IFERROR(INDEX(Sheet1!$A$2:$A$14,SMALL(IF(($M2=Sheet1!$C$2:$C$14)+($N2=Sheet1!$C$2:$C$14),ROW(Sheet1!$F$2:$F$14)-ROW(Sheet1!$F$2)+1),COLUMN(A1))),"")

I have put the expected result on Sheet2 which just shows one entry per lookup.

Thanks

Paul

**Glenn Kennedy** · 07-12-2019, 03:41 AM

No need for an array-entered formula:

=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$F$2:$F$14)/((($M2=Sheet1!$C$2:$C$14)+($N2=Sheet1!$C$2:$C$14))*(COUNTIF($A2:A2, Sheet1!$A$2:$A$14)=0)),1)),"")

copied across and down.

**pareid** · 07-12-2019, 04:04 AM

Thank you so much Glenn for your reply and help.

That works great in my example worksheet but when I put it into my master spreadsheet that has a lot of data it just returns zeros. I have adapted the obvious parts of the formula e.g. changed the $14 to $5000 to take account of the extra rows. Is there anything else I would need to change e.g. in the AGGREGATE statement.

Because I cut my example spreadsheet down for simplicity I have shown the formula when I put it into my master spreadsheet. Sheet1 is called (CopiedFUM). It just has some more columns and rows.

=IFERROR(INDEX(CopiedFUM!$B:$B,AGGREGATE(15,6,ROW(CopiedFUM!$AX$2:$AX$6000)/((($AD2=CopiedFUM!$G$2:$G$6000)+($BU2=CopiedFUM!$G$2:$G$6000))*(COUNTIF($A2:A2, CopiedFUM!$B$2:$B$6000)=0)),1)),"")

Thanks again for your help.

Regards

Paul

**Glenn Kennedy** · 07-12-2019, 04:11 AM

What cell have you put the formula in?

**pareid** · 07-12-2019, 04:13 AM

Hi Glenn,

Its in E2 and then copied across and down.

Thanks

Paul

**Glenn Kennedy** · 07-12-2019, 04:19 AM

=IFERROR(IF($AD2="","",INDEX(CopiedFUM!$B:$B,AGGREGATE(15,6,ROW(CopiedFUM!$AX$2:$AX$6000)/((($AD2=CopiedFUM!$G$2:$G$6000)+($BU2=CopiedFUM!$G$2:$G$6000))*(COUNTIF($E2:E2, CopiedFUM!$B$2:$B$6000)=0)),1))),"")

The required fix, and a correction.

**pareid** · 07-12-2019, 04:45 AM

Hi Glenn,

I am getting zeros returned so I have done another example file that has all the columns in it and I have deleted any sensitive data and just left the fields populated that the formula looks at. I thought this might be easier for you to look at but I am struggling to upload it to the site. The formula is in cell E2 in the 'DFM L T Bronze Removed' worksheet. I will keep trying to upload it for you.

Thanks

Paul

**pareid** · 07-12-2019, 04:48 AM

Hi Glenn,

It does say I have circular references in the formula.

Thanks

Paul

**Glenn Kennedy** · 07-12-2019, 04:49 AM

Save it as an .xlsb or zip it. File size limits are bigger

**pareid** · 07-12-2019, 04:54 AM

Here you go Glenn,

Thanks

Paul

**Glenn Kennedy** · 07-12-2019, 05:06 AM

My mistake..

Try:

=IFERROR(IF($AD2="","",INDEX(CopiedFUM!$B:$B,AGGREGATE(15,6,ROW(CopiedFUM!$AX$2:$AX$6000)/((($AD2=CopiedFUM!$G$2:$G$6000)+($BU2=CopiedFUM!$G$2:$G$6000))*(COUNTIF($D2:D2, CopiedFUM!$B$2:$B$6000)=0)),1))),"")

Row 2 is blank as there is no exact match from the AD2/BU2 cells.

**pareid** · 07-12-2019, 05:45 AM

Thanks Glenn,

That looks like it has worked. I will carry on looking through the data and see if I notice anything not quite right but looks great so far.

Appreciate all the help

Regards

Paul

**pareid** · 07-12-2019, 10:08 AM

Hi Glenn,

that does look like its working great. Just a quick question. Do you know if I wanted to also bring through the corresponding value in column S of the copiedFUM worksheet i.e. I bring through column B and the corresponding value in column S, is that possible?

Thanks

Paul

**Glenn Kennedy** · 07-12-2019, 10:34 AM

Your description is ambiguous.

Bring through... in the same cell, or in another one?

For each company, will there be one plan number, or several. If several, what results do you want to see.

Please modify your sample sheet and post it in a new post on this threead.

**pareid** · 07-12-2019, 10:49 AM

Hi Glenn,

Yes sorry, it is a bit ambiguous. There may be more than one plan number per company but happy for it to be in the same cell e.g. SIS 10043567 400787637, so you only have one cell per company still but could have multiple plan numbers separated with a space

I hope that makes sense.

Thanks

Paul

**Glenn Kennedy** · 07-12-2019, 10:52 AM

That may need VBA... Is that possible?

**pareid** · 07-12-2019, 10:54 AM

Hi Glenn,

Yes VBA is possible if that's the best way to go.

Thanks

Paul

**Glenn Kennedy** · 07-12-2019, 11:14 AM

Unfortunately, I'm no good with VBA. I had one option up my sleeve, but it didn't work. I'll ruminate on this for a little longer...

**pareid** · 07-12-2019, 11:37 AM

No problem, thanks again.

**Glenn Kennedy** · 07-12-2019, 12:42 PM

Another senior moment. DO NOT copy this into your real sheet yet. Check it out on this one first. Let me know if it's what you want.

It's an array formula. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Enable macros on opening.

**pareid** · 07-12-2019, 03:04 PM

Hi Glenn,

Yes that looks perfect.

Thank you.

Regards

Paul

**Glenn Kennedy** · 07-12-2019, 03:06 PM

Woo Hoo!!You're welcome.

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

**Glenn Kennedy** · 07-12-2019, 03:08 PM

When transferring to your real sheet... copy the code in module 1 to a module your real sheet and save as xlsm.

**pareid** · 07-30-2019, 05:28 AM

Hi Glenn,

I hope you are well. I hope you don't mind me asking a quick question regarding the VBA code you helped me with recently. It has been brilliant and working really well. I just wanted to ask you whether it was possible/easy to amend the code so that it also brings through column C from the 'CopiedFUM' tab? As an example I have attached the same example spreadsheet you created for me and put a highlighted section on the 'DFM L T Bronze Removed' tab.

In essence I am just trying to append the data in 'CopiedFUM' column C to the end of the plan number.

Thanks

Paul

**AliGW** · 07-30-2019, 05:30 AM

The thread is marked as solved - you might wish to change this for now.

**Glenn Kennedy** · 07-30-2019, 09:47 AM

Yes.... and it was easier than I'd feared!!

Formula:

Please Login or Register  to view this content.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

**pareid** · 07-30-2019, 12:17 PM

Thank you Glenn, that is absolutely perfect.

Really appreciate it.

Cheers

Paul

**Glenn Kennedy** · 07-30-2019, 12:18 PM

You're welcome.

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Re: INDEX MATCH SMALL but ignore duplicates

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] INDEX/MATCH with duplicates add up

[SOLVED] INDEX - MATCH with duplicates

Bookmarks

Bookmarks

Posting Permissions