VLOOKUP Across Two Columns

[SamuelT]

Hi all,

I'm trying to use VLOOKUP to populate a number of cells. However, fo
my cells to be filled I need VLOOKUP to look at two columns, rathe
than one.

The reason for this is that the spreadsheet is dealing with a number o
teams in the business. Within the teams there are a number of roles tha
have identical job titles in them. For example, I have a team calle
'Services' and one called 'Marketing' within both these teams there i
a 'Project Manager' and a 'Business Analyst'.

So, what I need Excel to do is first look at the team, then look at th
job title, and from that VLOOKUP in the rest of my spreadsheet t
populate the cells. I've fiddled around with a few IF and AND formulas
but not joy.

Any ideas?

TIA,

Samuel

 

[Peo Sjoblom]

=INDEX(Range3,MATCH(1,(Range1="team")*(Range2="jobtitle"),0))

entered with ctrl + shift & enter

where Range3 is the range where you want the result returned from

 

[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.