Vlookup using two columns

[Daniel Bonallack]

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

[Montrose77]

Ok, this is a very crude solution, but it works...

Enter the following formula into E2:

=A2&B2

And in F2:

=A2&"Managing Director"

Then in D2:

=INDIRECT(ADDRESS(MATCH(F2,E:E,0),3))

This formula can be Auto-filled as far down as required. You can also hide columns E&F if you like.

[Dave Peterson]

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

[Chris Lavender]

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" <[email protected]> wrote in
message news:[email protected]...
> 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