G
Guest
Hi! I am trying to do a lookup based on two criteria -one being in two
columns. I have one worksheet that has the entity and department bumber and
one that has account rep, entity, range of department numbers (see example).
What I want to do is bring the account rep into the first worksheet by
matching the entity and if the department is >= to beginning dept and <=
ending dept. I have done an index and match based on entity and begining
dept and if that is an NA error match entity and ending dept. I cant figure
out what the formula needs to be if the department number falls within that
beg-end range.
Example:
Ent Begin Dept End Dept Acct Rep
1 6110 6119 dflood
If Ent = 1 and dept = 6115, bring over acct rep "dflood"
Does anyone know of a formula that could help?
columns. I have one worksheet that has the entity and department bumber and
one that has account rep, entity, range of department numbers (see example).
What I want to do is bring the account rep into the first worksheet by
matching the entity and if the department is >= to beginning dept and <=
ending dept. I have done an index and match based on entity and begining
dept and if that is an NA error match entity and ending dept. I cant figure
out what the formula needs to be if the department number falls within that
beg-end range.
Example:
Ent Begin Dept End Dept Acct Rep
1 6110 6119 dflood
If Ent = 1 and dept = 6115, bring over acct rep "dflood"
Does anyone know of a formula that could help?
100,MATCH(1,(A1:A100=1)*(B1:B100<=6115)*(C1:C100>=6115),0))