If the cell range from B1:C10 contains the number in cell D1 then return the parallel name in cell range A1:A10 to cell E1. .........A.........B.........C.........D.........E

1....JACK .....10........15........4.......LACY

2....JILL.........11........7.........5.......

3....KYLE.......8.........12.......6.......

4....LACY.......4.........9.........7.......JILL

message edited by lou1707

First, a couple of posting tips... 1 - When posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "What Is The Formula ..." we wouldn't be able to tell one question from another and the Archives would essentially be useless.

I have edited the subject line of your post to show you what I mean. Scan the list of questions in this forum to see the types of Subject Lines that others have used.

2 - Please click on the

How-To linkat the end of this post and read the instructions on how to format example data so that it is easier for us to read.As for your question, I see that you put Jill in E4, which I assume means that you want to "match" the name from A1:A10 based on all of the numbers in D1:D10, not just D1. Assuming that is correct, the following formula should work for you. Paste it into E1 and drag it down.

=IF(COUNTIF($B$1:$C$10,D1)=0,"",

IFERROR(INDEX($A$1:$A$10,MATCH(D1,$B$1:$B$10,0)),

INDEX($A$1:$A$10,MATCH(D1,$C$1:$C$10,0))))If you would like an explanation for how the formula works, just let me know.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll