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

Do you think state sales tax should be charged in the United States on all Internet sales?

Discuss in The Lounge

Poll History