Find column where x exists

[MAP]

I need your assistance with a small project I am working on. I know what I am going to ask you will be trivial to you considering the good knowledge many of you have of Excel functions and formulas, but please educate me.

If I have a list of names in a column... if there are three or four columns (A1:C)... If I enter a name in a cell (B9), how can I have Excel find out which column that name is in (C9)?

** since I am not familiar with VBA, I would hope you would offer a suggestion that involved a function/formula **

example:
.........a..............b..............c..........d..........e.....
1.COLUMNA.....COLUMNB.....COLUMNC
2..Jack............Bill..............****
3..Jill...............Bob............Tom
4..James..........Buffy..........Harry
5..Judy............Bobby.........John
6..Jake............Bess...........Jane
7..Jesse..........Brian
8
9.................ENTER.NAME...THIS.NAME.IS.IN
9..................Brian.............COLUMNB
9..................Jane.............COLUMNC
9..................James...........COLUMNA
9..................Bobby...........COLUMNB
9..................Tom.............COLUMNC

just out of curiosity, How can I have Excel notify user if one or more names on the either columns were identicle? I have a list of names in the columns. I want to input names out of that list in another column. How can I have Excel check to see if a name was repeated in the inputed list?

I'll appreciate all the help I can get. Thank you.

 

[sanrv1f]

try this

Excel Workbook
	A	B	C
1	Jack	Bill	****
2	Jill	Bob	Tom
3	James	Buffy	Harry
4	Judy	Bobby	John
5	Jake	Bess	Jane
6	Jesse	Brian
Sheet2

Excel Workbook
	A	B
9	Brian	2
10	Jane	3
11	James	1
12	Bobby	2
13	Tom	3
Sheet2

#VALUE!

 

[Sandeep Warrier]

#VALUE!

Entered with Ctrl+Shift+Enter

Just the SMALL part of the formula also gives the required result.

Excel Workbook
	A	B	C
1	Jack	Bill	****
2	Jill	Bob	Tom
3	James	Buffy	Harry
4	Judy	Bobby	John
5	Jake	Bess	Jane
6	Jesse	Brian
7
8
9	Brian	2
10	Jane	3
11	James	1
12	Bobby	2
13	Tom	3
14	John	3
15	Jack	1
Sheet1

#VALUE!

Entered with Ctrl+Shift+Enter

I
just out of curiosity, How can I have Excel notify user if one or more names on the either columns were identicle? I have a list of names in the columns. I want to input names out of that list in another column. How can I have Excel check to see if a name was repeated in the inputed list?

I'll appreciate all the help I can get. Thank you.

I feel doing a COUNTIF would be the best way. COUNTIF(Range, Criteria)>1 will denote multiple values.<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[MAP]

sandeep.warrier, I thank you for your suggestion. This is a process that will do the trick and is good to work with. It is a simple method, yet it solves my problem...

But how could we solve this problem if the occasional changing of names was a factor? Using your idea, I would need to change the names in two places whenever the need to change the name was a necessity.

Can anyone suggest a formula/function that would search an array of columns? The VLOOKUP and the HLOOKUP only work with one column at a time. I am looking for soming similar to using INDEX - only reverse. With INDEX, Excel gives me a particular cell within in a range given a row and column. I want to know if there is a function/formula that will look at the range and give me the row and column when given a cell.

For now, the suggestion offered by sandeep.warrier will be utilized - in hopes that someone will come up with a more user-friendly method. THANK YOU.

 

[Sandeep Warrier]

But how could we solve this problem if the occasional changing of names was a factor? Using your idea, I would need to change the names in two places whenever the need to change the name was a necessity.

Can anyone suggest a formula/function that would search an array of columns? The VLOOKUP and the HLOOKUP only work with one column at a time. I am looking for soming similar to using INDEX - only reverse. With INDEX, Excel gives me a particular cell within in a range given a row and column. I want to know if there is a function/formula that will look at the range and give me the row and column when given a cell.

I don't quite understand what you mean. Perhaps you could post some sample data. You could use Excel Jeanie to post data. Instructions on how to use it are given on the website.

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[MAP]

sandeep.warrier, I must appologize. Before I implimented your SMALL function, I was misunderstanding your method. I see how your way doing is very useful and efficient.

THANK YOU.