Index/Match and Concatenate

[jwburritt]

Hello: I have a list of first and last names and amounts in a range. I want to find the amount that corresponds to both the first and last names and return that value. I've used Index/match in the past when there was only the last name and it worked fine. Now, when I added the first name, I tried simply to concatenate the two and run the index/match. No luck. I've tried adding parentheses but that didn't work either. Any help would be appreciated!

=INDEX(E40:E41,MATCH(B18&C18,B40&C40:B41&C41,0))

[TABLE="width: 492"]
<tbody>[TR]
[TD]First[/TD]
[TD]Last[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Evans[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Smith[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]


Thank you!

 

[Fluff]

You can use either
=INDEX(E40:E41,MATCH(B18&"|"&C18,B40:B41&"|"&C40:C41,0))
or
=INDEX(E40:E41,MATCH(B18&"|"&C18,INDEX(B40:B41&"|"&C40:C41,0),0))

The first one needs to be array entered with Ctrl Shift Enter.
The 2nd one doesn't

 

[jwburritt]

Thanks, Fluff. Works perfectly!

 

[Fluff]

You're welcome & thanks for the feedback