Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
In column A I have companies, in column B I have titles, in column C, I have
names Column A repeats companies, as there are a bunch of titles for each company. In column D, I would like to return the name of the "Managing Director" for each company (so it will be one name repeated for each company), based on looking up the title in column B in combination with each unique company eg IBM, Director, Bob [in D, returned value = "Tim"] IBM, Managing Director, Tim [in D, returned value = "Tim"] IBM, Vice President, Mary [in D, returned value = "Tim"] 3M, Director, Sarah [in D, returned value = "Robyn"] 3M, Managing Director, Robyn [in D, returned value = "Robyn"] Thanks in advance Daniel |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() Ok, this is a very crude solution, but it works... Enter the following formula into column E: =A2&B2 And in column F: =A2&"Managing Director" Then in column D: =INDIRECT(ADDRESS(MATCH(F2,E:E,0),3)) You can hide columns E&F if you like. -- Montrose77 ------------------------------------------------------------------------ Montrose77's Profile: http://www.excelforum.com/member.php...o&userid=18191 View this thread: http://www.excelforum.com/showthread...hreadid=492239 |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
You can use this kind of syntax:
=index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. == But I got confused about what column is what. Daniel Bonallack wrote: In column A I have companies, in column B I have titles, in column C, I have names Column A repeats companies, as there are a bunch of titles for each company. In column D, I would like to return the name of the "Managing Director" for each company (so it will be one name repeated for each company), based on looking up the title in column B in combination with each unique company eg IBM, Director, Bob [in D, returned value = "Tim"] IBM, Managing Director, Tim [in D, returned value = "Tim"] IBM, Vice President, Mary [in D, returned value = "Tim"] 3M, Director, Sarah [in D, returned value = "Robyn"] 3M, Managing Director, Robyn [in D, returned value = "Robyn"] Thanks in advance Daniel -- Dave Peterson |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi Daniel
Simplest might be to add another column on the left, with a concatenation of columns A and B ie, IBMDirector IBMManaging Director IBMVice President 3MDirector 3MManaging Director and then have your lookup look for a concatenation eg, =VLOOKUP(B15&"Managing Director",A:D,4,0) HTH Best rgds Chris Lav "Daniel Bonallack" wrote in message ... In column A I have companies, in column B I have titles, in column C, I have names Column A repeats companies, as there are a bunch of titles for each company. In column D, I would like to return the name of the "Managing Director" for each company (so it will be one name repeated for each company), based on looking up the title in column B in combination with each unique company eg IBM, Director, Bob [in D, returned value = "Tim"] IBM, Managing Director, Tim [in D, returned value = "Tim"] IBM, Vice President, Mary [in D, returned value = "Tim"] 3M, Director, Sarah [in D, returned value = "Robyn"] 3M, Managing Director, Robyn [in D, returned value = "Robyn"] Thanks in advance Daniel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
adding three consecutive columns | Excel Worksheet Functions | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
vlookup for multiple columns | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
VLOOKUP won't work | Excel Worksheet Functions |