Search named ranges for matching cell contents

[sedwardson]

Hi all and thanks for all and any help. I have one workbook with two worksheets. I have a list of references on the first worksheet and a HUGE list of references on the second worksheet but they are grouped into two named ranges. I would like to write an equation where the reference in worksheet one, lets say 401YV480628 matches a reference on worksheet two in named range called "Yes" and then displays that named range i.e. "yes" next to it. If the match was found in the second named range ("No") I would like it to report that. So it would look something like this:

March 2023	401YV480628	Yes
February 2023	410HF569447	Yes
February 2023	414GA580218	No

I hope this makes sense. Thanks again for any help.

 

[sedwardson]

Yes. Here is the screen shot of the ranges....

 

[Herakles]

The named ranges need to contain the references as in your original post.

The formula looks in these ranges for the reference.

 

[sedwardson]

The named ranges need to contain the references as in your original post.

The formula looks in these ranges for the reference.

Hiya, the references in the first worksheet are in the named ranges on the second worksheet.

 

[Herakles]

I've tried using the MATCH function with a two column range to look up in and I get an error so I suggest that
you change the Family_Numbers and Sandras_Numbers named ranges to single column ranges. If this is not possible then set two more named ranges up just for this purpose.

 

[sedwardson]

It will return a zero length string if the reference is not found in either of the named ranges.
It will also return a zero length string if the named ranges cannot be found.

Are your named ranges called "Family_Numbers" and "Sandras_Numbers"?

Hi Herakles, thanks for all your help. It certainly pushed me in the right direction and I finally came up with something close to what I was asking but does the same thing. It uses your original idea of the yes/no result but just using friendly names (and the ones I wanted to get from the named ranges). The Cell reference changes per row so it looks at a different lookup each time but thats as complex as it got This was solution in the end but I couldn't have got there without you. Thanks.

=IF(COUNTIF('All Bond Number Ranges'!$A:$D,B2),"Family","Sandra's")

 

[Herakles]

Glad to be of some help.