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))

First	Last	Amount
Mary	Evans	100
John	Smith	200

<tbody>
</tbody>

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