Vlookup non-unique data, multiple values - array formula?

**doritos** · 06-12-2011, 02:39 AM

Dear all,

Understand that we can use an array formula (Ctrl + Shift + Enter) to vlookup multiple values for the following Raw Data:

Raw Data
A 15
B 15
C 20
A 20
A 25
A 26
B 27

Formula as follows:
=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)

Results:
A 15
20
25
26

However, is there a way to drag the results horizontally instead of vertically?
I want it to look like this:

Results
A 15 20 25 26
B 15 27
C 20

Please help!! Much appreciated!

**Haseeb Avarakkan** · 06-12-2011, 03:07 AM

Hello doritos, Welcome to the forum!

Use COLUMNS, instead of ROW(1:1). see the attached.

**doritos** · 06-12-2011, 08:53 PM

Hi Haseeb, thanks!!! Much appreciated!

In this case, my raw data would have to be in columns as well?

Any way to tranpose it instead? (ie. leave the raw data in rows, but have the results in columns)

PS: apologies if your excel already addresses this, am struggling to open the file with the security settings from my office machine.

**Haseeb Avarakkan** · 06-12-2011, 09:13 PM

COLUMNS() used to get the appropriate row numbers,

Try these, may be easier to understand.

On a blank sheet enter some numbers in A1:A20.

In B1, use this then copy across.

=INDEX($A$1:$A$20,COLUMNS($B1:B1))

in C1, you will get,

=INDEX($A$1:$A$20,COLUMNS($B1:C1))

You can see A1:A20 values transposed. here, COLUMNS($B1:B1) is same as ROW(1:1), COLUMNS($B1:C1) is same as ROW(2:2). ROW only expand when you copy down not across. Important is you need to lock the first reference. see the $ sign, you can also use COLUMN(A:A), COLUMN(A1).. COLUMNS($A:A),

COLUMNS($B1:B1) means, how many column in B1:B1? answer 1. B
COLUMNS($B1:C1) means, how many column in B1:C1? answer 2, B & C
etc..

If you used COLUMN(B1:B1) answer is 2, not 1. This is looking column no: of B1, which is 2nd col. COLUMNS is looking for column no: between the start & end reference.

I am not well in explaining something

Hope this helps.

**doritos** · 06-13-2011, 09:55 PM

Hi Haseeb.

This is great! However, I tried it on my own and it did not work

Will the formula work if I link different sheets? I made sure I locked the right values, etc.

**Haseeb Avarakkan** · 06-13-2011, 10:10 PM

Would you please post the exact formula you tried?

**doritos** · 06-14-2011, 07:18 AM

=IFERROR(INDEX($S:$S,SMALL(IF($R$2:$R$450=$A3,ROW($R$2:$R$450)),COLUMNS($K3:K3))),"")

Column R and S are actually formulas linked to another sheet. And values in both R and S columns may look like this:

0
0
0
A
A
B
A
0
C
0
A
B

"0" because of a no match issue on a separate sheet.

Is there a problem with this? Thanks once again for helping out!