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.

 

[Herakles]

Try this.

You may need to change the cell reference

=IF(NOT(ISERROR(MATCH(B2,Yes,0))),"Yes",IF(NOT(ISERROR(MATCH(B2,No,0))),"No",""))

 

[sedwardson]

Try this.

You may need to change the cell reference

=IF(NOT(ISERROR(MATCH(B2,Yes,0))),"Yes",IF(NOT(ISERROR(MATCH(B2,No,0))),"No",""))

Hi Herakles, thanks for the prompt reply. That reports "Yes" or "No" and not the name of the range that the reference is located in as far as I can see. That being said, I'm still very new to VBA so I'm happy to be corrected

 

[Herakles]

So what do you want to be displayed in column 3?

 

[sedwardson]

So what do you want to be displayed in column 3?

the name of the range the reference is found in. Thanks.

 

[Herakles]

You question seems to state that the named ranges are called 'Yes' and 'No'.

What are they called?

 

[sedwardson]

You question seems to state that the named ranges are called 'Yes' and 'No'.

What are they called?

They are actually called "Family_Numbers" and Sandras_Numbers", but thought it might be easier with yes and no .......Apparently I was wrong !

 

[Herakles]

Try this. We only know what you tell us.
I've not tested this.

=IF(NOT(ISERROR(MATCH(B2,Family_Numbers,0))),"Yes",IF(NOT(ISERROR(MATCH(B2,Sandras_Numbers,0))),"No",""))

 

[sedwardson]

Apologies for the miscommunication. I have tried the IF statement and it doesn't return anything.....

 

[Herakles]

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"?