index, match lookup using catenated values not working

[Guest]

Hi,

Here's my formula this is giving me grief:
{=INDEX(_Map1,MATCH($C$3,NameRange&"("&AgreementRange&")",1),3)}

My formula doesn't seem to be working. My value in $c$3 is "lysell,
kent(17)", and this is value I'm using to lookup the row where this value is
found (on the Summary sheet) in the "_Map1" range. However, I get this lookup
value by catenating "lysell, kent" in column c with "17" in column d
(including concatenating "(" and ")" around the AgreementRange). I would like
the 3rd column in my "_Map1" range. I've named the employee names in column c
"NameRange" and the agreement numbers in column d "AgreementRange".

Any help would be immensely appreciated!

Thanks in advance,

 

[Guest]

Kent,
It's not absolutely clear to me exactly what is being compared
but if you want to match 'Kent' with a column 'Lysell' with another and the
'17' with another, then using MATCH it can be done like this (as an example):

MATCH(1,(A1:A100)="kent")*(B1:B100="Lysell")*(D1100=17),0) and entered
with Ctrl+Shift+Enter

So your INDEX formula would be something like:

=INDEX(_Map1,MATCH(1,(A1:A100)="Kent")*(B1:B100="Lysell")*(D1100=17),0),3)

again entered with Ctrl+Shift+Enter

If NameRange" contains "Lysell,Kent", then perhaps this is what you want:


=INDEX(_Map1,MATCH(1,(NameRange=$C$3)*(AgreementRange=17),0),3)

where $C$3="Lysell,Kent"

HTH

 

[Guest]

Thanks Toppers! I'll give it a try. Kent.