lookup to return multiple entries

  • Thread starter Thread starter Suji
  • Start date Start date
S

Suji

Hi all...
I have a question.. I have a spreadsheet where i want to return the
alphabet corresponding to number.
for eg
I have two row
row 1 a b c d e f g h
row 2 0 1 0 0 1 0 1 0

Something like this... i want a function that will return the
corresponding alphabets if i give the no...
so if I give 1 i want it to return b,e,g

I used hlookup for this but it is returning only the first instance.
for eg.. only "b" not the rest....
how can i get the rest .. the row 2 keeps changing.. that is the
position of o's and 1's keeps changing when i click a button....

Thanks,
Suji
 
try this

put 0 or 1 in cell A5 and in B5 put this formula and drag it down
( once u enter the formula use Ctrl + shift + enter )

=IF(ISERROR(INDEX($A$1:$E$1,,SMALL(IF($A$2:$E$2=$A$5,COLUMN($A$1:$E
$2)),ROW(1:1)))),"",INDEX($A$1:$E$1,,SMALL(IF($A$2:$E$2=$A$5,COLUMN($A
$1:$E$2)),ROW(1:1))))
 
Try this:

Letters in the range A1:H1
Numbers in the range A2:H2
A3 = 1 or whatever number you want to use

Enter this array formula** in A4 and copy across to H4:

=IF(COLUMNS($A4:A4)<=COUNTIF($A2:$H2,$A3),INDEX($A1:$H1,SMALL(IF($A2:$H2=1,COLUMN($A1:$H1)),COLUMNS($A4:A4))-MIN(COLUMN($A1:$H1))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
thanks a lot both of you for ur suggestions...
I tried them and they are working....

Thanks a lot again...

Suji
 
Back
Top