How to look up a value in a list and return multiple corresponding values?

**arnabmit** · 04-22-2011, 03:34 PM

Here is the source data
Names in Column A, Scores in Column B
A 1
B 3
A 2
B 5
C 8
C 2
D 1
E 8
A 6

I need formulas which would return the following result:

A 1 2 6
B 3 5
C 8 2
D 1
E 8

I tried combining vlookup & hlookup, match index array, nested if... so far no results.

NOTE: each name can have upto 10 scores, names could be hundreds.

Help please...

**contaminated** · 04-22-2011, 03:45 PM

Say values A, B, C, D, E are entered manually. so in the cell next to A put this function, copy down then accross

=IF(COUNTIF($A$2:$A$10,$F4)>=COLUMNS($G3:G$3),SMALL(IF($F4=$A$2:$A$10,$B$2:$B$10,""),COLUMNS($G3:G$3 )),"")

Array Entered: e.g entered with CTRL+SHIFT+ENTER

**arnabmit** · 04-23-2011, 07:53 AM

Thank you so much! Worked perfectly!!!

**silver.eb** · 11-18-2011, 05:05 AM

Thanks, this file is perfect. However i've an issue. In column B i do not have numbers but word ( see example below) and therefore the formula is not working. Can you help me somehow?? thanks a lot!

Enrico

Example
A apple
B banana
...

**silver.eb** · 11-18-2011, 08:07 AM

solved on another thread:
http://www.excelforum.com/excel-2007...ing-words.html

**sunflowers** · 11-18-2011, 08:09 AM

Originally Posted by silver.eb

Thanks, this file is perfect. However i've an issue. In column B i do not have numbers but word ( see example below) and therefore the formula is not working. Can you help me somehow?? thanks a lot!

Enrico

Example
A apple
B banana
...

Formulas in cell C2 to extract unque values : A, B , C... automatically

PHP Code:


=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX($A$2:$A$10,MATCH(TRUE,INDEX(ISNA(MATCH($A$2:$A$10,$C$1:$C1,0)),0),0))))

If column B you do not have numbers, you enter this Formula in D2 :

PHP Code:


=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$C2,ROW($A$2:$A$10)-1,""),COLUMN(A:A)))))

Entered with CTRL+SHIFT+ENTER
Copy down then accross
Plz, See attachment file, thanks!

**MOOSAA** · 11-24-2011, 10:19 AM

Hi

I have this spread sheet which I have and I need the tab called "HomePage" be the main page where the user enter the post code in the post code box and search all sheets for the details from all sheets with the sheet title displayed under the 3 tabs

the field need to be populated are as follow:

Practice Name
Address
Post Code
Telephone Number
Clinical System
Team

I can attach the file if you require