cell reference

[ahhhmed]

Hi,

In A2:A10 there are numbers from 1 to 9

In B2:B10 there are names.


I want to make anothe list in B20 onward where I want to choose some of the names from B2:B10. What I need is how to show in A20 onwards the values from A2:A10 that correspond to the name I choose.


Any help is highly appreciated, thanx in advance

 

[Luke M]

Using the technique from here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


=IF(COUNTIF(B$2:B$10,B$20)<ROWS(A$20:A20),"",INDEX(A:A,SMALL(IF(B$2:B$10=B$20,ROW(B$2:B$10)),ROW(A1))))


formula assumes criteria name is in B20. Note that this is an array formula. Once you've got it input into one cell, just drag down to as far as you think will be needed.

 

[fred]

Hi Luke M, may be I misunderstood the question. I came up with just


INDEX($A$2:$A$10,MATCH(B20,$B$2:$B$10,0),1) and he can copy down.

 

[Luke M]

Hi Fred,


Taking a 2nd look, you may be right.

I want to make anothe list in B20 onward

I'm not sure if OP is generating a list of names that have a single corresponding value (go with your formula) or a single name with several corresponding values (my formula).

 

[ahhhmed]

Thanks everybody,

The formula is very good and it works well as long as I choose names from the original list, but if I choose a name that is not in the original list, the formula comes up with an error #N/A. Can we add something to the formula to make the error blank?

 

[Luke M]

Assuming you went with fred's formula:

=IF(COUNTIF(B$2:B$10,B20)<1,"",INDEX($A$2:$A$10,MATCH(B20,$B$2:$B$10,0),1))

 

[ahhhmed]

Thanks for the quick reply, Luke M. I wish we met in person, you would be a great teacher

 

[ahhhmed]

Can the last formula be used if I want to add a third column so that when I choose a name from column A the corresponding cells in B and C appear?

 

[Luke M]

Should be able to, just need to change the 1st arguement of the INDEX function.

 

[ahhhmed]

Thanks Luke but I could not figure out how to do it. Could you pls write an example formula?