• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

reference issue

A

ahhhmed

Member
Hi all,

I have this work sheet

column A has numeric values that are fixed.

column B has dropdown lists in each cell with names.

I may choose whatever name in each cell in column B

My problem:

Column C has all the names the dropdown list displays.

I want cells Column D,E,F to show the numeric values from column A that correspond to the names in column B

Any help is highly appreciated
 
INDEX($A$2:$A$17,MATCH($B$2,$C$2:$C$17,0),)&" "&$B$2


assuming

A2:A17 are the figures

B2=the choice from the pull down list

C2:C17 are the names


the result would be someting like

76 Nancy or

24 Eric

39 Harry


Is that how you want it? but it will only appear in one cell in column D,E or F unless you want to duplicate the formula. I don't quite understand why you mentioned columns D, E and F. what is your intention? What is the exact "look" of the final result you are looking for??
 
Thanks Fred,

I mentioned D, E, and F because the names in B may appear more than once, so if a certain name appears in B2, B5, and B7, the values should appear next to it in D, E, and F
 
How many pull down menus are there in B then? And how are they going to correspond to column D, E and F? there is only one figure to one name on each line. I don't understand how you are going to proliferate the figure and the chosen name on 3 cells on the same line in columns D E and F.


side note: if there is more than one pull down menue, I "assume" the lists are pointing to the same list. if so, remove the $ sign around B2 and copy down.
 
This is what the end result should be:


A B C D E F

1 Ann Ann 1 5

2 John John 2 3 9

4 Sami Sami 4 7

5 Ann

7 Sami

3 John

9 John
 
I can understand your Ann 1 but I don't get the "5".


I understand how to get "John 2" but I don't know what you mean by "3 9".


I understand your "Sami 4" but don't quite get the "7".


can you elaborate please? thx.
 
Ann appears twice: next to 1 and next to 5 in A

John appeared three times, next to these values: 2, 3 and 9

the same for the rest,

so all appearances are included in D E F
 
then wouldn't a pivot table be a better alternative? if you change anything on the pulldown menu, all you have to do is refresh the pivot table. beat any formula in my opinion.
 
After trying the pivot table several times, I found that it is not the best solution, beacuse I want to relate cells D E and F with other sheets.
 
Dear Ahhhmed,


In the sample sheet you have given, i think the following formula would work:


Assuming: B2:B8 has the names, A2:A8 has the numbers you want, C2,C3 etc have the dropdowns.

in cell D2

=LARGE(($B$2:$B$8=$C2)*$A$2:$A$8,COLUMN()-3)


this is an array formula and require ctrl + shift + enter


The caveat is:

If the name in selected appears less than 3 times, the results are 'zero'.:you can insert a if(xx=0, then, then) to adjust.


And remember, there is always a better way.
 
Thank you anupagarwal06,

The formula works pretty well, except for one thing: when I put the value 6/1 in A which is formatted as text, it will return to 40695 in D. I want the same 6/1 or whatever value to appear in D. I also formatted D as text
 
The above formula works only for numbers. the formula below works for other content.


=IFERROR(INDEX($A$2:$A$8,LARGE(IF($B$2:$B$8=$C2,ROW($A$2:$A$8),0),COLUMN()-3)-1)


Also remember if there are more than 3 appearances, you will have to extend to more than 3 columns.
 
Thaks dear,

The formula shows an error at -1 which is at the end of the formula. Excel highlights it black and the formula does not work
 
The message says that I need to enter a paranthesis ( or ) at the right place. I put it at the end, another message appeared saying you entered a few arguments for this function
 
Hi ahhhmed,


Try this formula :


=IFERROR(INDEX($A$2:$A$8,LARGE(IF($B$2:$B$8=$C2,ROW($A$2:$A$8),0),COLUMN()-3)-1),"")
 
How can this formula be amended?

=IFERROR(INDEX($A$2:$A$8,LARGE(IF($B$2:$B$8=$C2,ROW($A$2:$A$8),0),COLUMN()-3)-1),"")

When the following changes take place:

Numbers are in column B19:B49

Names are in C19:C49

short list of names are in C60:C70

results are in D,E,F60:70

Please try it and help me with the amended formula
 
Try this array formula in D60:


=IFERROR(INDEX($B$19:$B$49,LARGE(IF($C$19:$C$49=$C60,ROW($B$19:$B$49),0),COLUMN()-3)-18),"")
 
This formuls worked well, thanks to you GCExcel.


Now, vice-versa:


Numbers are in column B19:B49

Names are in C19:C49

short list of NUMBERS are in C80:C85

results are in D,E,F80:85

results should show all the names that correspond a certain number.


Any ideas?
 
This should work, in D80 :


=IFERROR(INDEX($C$19:$C$49,LARGE(IF($B$19:$B$49=$C80,ROW($B$19:$B$49),0),COLUMN()-3)-18),"")
 
You must log in or register to reply here.
Back
Top