Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 9th 05, 06:32 PM posted to microsoft.public.excel.worksheet.functions
Daniel Bonallack
 
Posts: n/a
Default Vlookup using two columns

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   Report Post  
Old December 9th 05, 07:19 PM posted to microsoft.public.excel.worksheet.functions
Montrose77
 
Posts: n/a
Default Vlookup using two columns


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   Report Post  
Old December 9th 05, 07:57 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default Vlookup using two columns

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   Report Post  
Old December 9th 05, 08:02 PM posted to microsoft.public.excel.worksheet.functions
Chris Lavender
 
Posts: n/a
Default Vlookup using two columns

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,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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding three consecutive columns Darin Gibson Excel Worksheet Functions 1 November 22nd 05 09:50 PM
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns foofoo Excel Discussion (Misc queries) 1 April 2nd 05 12:02 AM
vlookup for multiple columns MXC Excel Worksheet Functions 6 March 4th 05 10:59 PM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 01:09 PM
VLOOKUP won't work o1darcie1o Excel Worksheet Functions 4 December 28th 04 09:05 PM


All times are GMT +1. The time now is 02:32 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017