INDEX and SMALL help

**markgilmore** · 03-02-2015, 01:05 AM

Hi Guys

I have data in the cell ranges A1 to B4566. In column A are postcodes, in column B are email addresses. There are multiple instances of postcodes per email address, on separate rows. For example:
Col A Col B
0100 Email 1
0100 Email 2
0100 Email 3
0200 Email 4
0200 Email 5
0300 Email 6
0300 Email 7

I have a list of postcodes I need to extract Emails for, in the cell range R1:R308. I also have another list in T1:T198.
For example, if R1 contains 0100, R2 contains 0200 etc, I want to be able to drag the formula both horizontally and vertically to capture all data, so I'm left with something like:

0100	email 1	email 2	email 3
0200	email 4	email 5
0300	Email 6	Email 7

Where the number values are the list in column S and the emails are the index formula.

At the moment here is what I have but it is giving me a #num error:

Please Login or Register  to view this content.

Thanks in advance for any help.

**FlameRetired** · 03-02-2015, 01:10 AM

Formula doesn't work.
Deleted by FR

**markgilmore** · 03-02-2015, 01:15 AM

Originally Posted by FlameRetired

What happens when you put the SMALL argument in the columns part of INDEX like this:

Formula:

Please Login or Register  to view this content.

same deal unfortunately

#num

**:) Sixthsense :)** · 03-02-2015, 01:20 AM

Your current formula is perfect #Num in your formula refers that there is no matching value found in column A

**markgilmore** · 03-02-2015, 01:27 AM

Originally Posted by :) Sixthsense :)

Your current formula is perfect #Num in your formula refers that there is no matching value found in column A

You're right - if I change the first value in A1 to match the value in R1 (in this case 3000) it works, however the first few hundred or so in the list aren't in column R1 however I don't want to delete them because I want to re-use this formula for other lists, is there any way around this?

**:) Sixthsense :)** · 03-02-2015, 01:32 AM

Can you please attach a sample file with expected result so that it will be easy for us in giving the exact solution

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**markgilmore** · 03-02-2015, 01:43 AM

Thanks for your help - here's the spreadsheet, hopefully it clears things up.

desensitized list.xlsx

**:) Sixthsense :)** · 03-02-2015, 01:48 AM

Column-A Values are Text Numbers
But R1 Value is Real Number

So instead of R1 change it to TRIM($R1) so that the trim function will make it as Text Number

=INDEX($B$1:$B$4566,SMALL(IF(TRIM($R1)=$A$1:$A$4566,ROW($A$1:$A$4566)-ROW($A$1)+1),COLUMN(A1)))

**markgilmore** · 03-02-2015, 02:52 AM

Originally Posted by :) Sixthsense :)

Column-A Values are Text Numbers
But R1 Value is Real Number

So instead of R1 change it to TRIM($R1) so that the trim function will make it as Text Number

=INDEX($B$1:$B$4566,SMALL(IF(TRIM($R1)=$A$1:$A$4566,ROW($A$1:$A$4566)-ROW($A$1)+1),COLUMN(A1)))

What a silly little thing, thanks so much!