Checking values from a range in a particular cell

[Mark McInerney]

Hi Folks - Thanks for taking a look at this.

I have a few ranges of data:

There are many more ranges, Range 1 thru Range 20. As per the above, Range 1 and 2 and so on, have a listing of data entries e.g. Range 1 has Keary, Murphy, Kelly, Malluisk, Kernan and littleton - names will be added and removed from time to time.

The listing column needs to be able to determine if any word in it's listing, appears in any of the cells across all the ranges.

The logic for Cell F3 needs to check of any of the entries in Range 1 are in Cell E3, or, in Range 2 are in E3 and tag it accordingly. There can be anything up to 20 ranges.

Any help appreciated - Thanks - Mark.

 

[Anbuselvam]

Try in Cell F3

=IF(SUMPRODUCT(COUNTIF(INDIRECT("Range 1"), "*"&E3&"*"))>0, "Range 1", IF(SUMPRODUCT(COUNTIF(INDIRECT("Range 2"), "*"&E3&"*"))>0, "Range 2", ""))

 

[Fluff]

Could any of the words in the Listings col appear in more than one of the ranges? If so what should the output be?

 

[Mark McInerney]

Hi Fluff,

Hope all good with you - no - the values will be unique - they will only appear in one of the ranges.

Thanks for taking a look Anbuselvam - will try ot out today and let you know.

 

[Fluff]

So with your example you couldn't have Keary in Range1 & John in Range3

 

[Mark McInerney]

Correct

 

[Fluff]

Ok
Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2		Range 1	Range 2	Range 3	Range 4
3		a	f	i	o		a z	Range 1
4		b	g	j	p		x h	Range 2
5		c	h	k	q		n	Range 3
6		d		l	r		c y	Range 1
7		e		m
8				n
9
Main

Cell Formulas
Range	Formula
H3:H6	H3	=LET(h,HSTACK(TOCOL(B3:E9,1),TOCOL(IF(B3:E9="",x,B2:E2),2)),BYROW(G3:G6,LAMBDA(br,CONCAT(IFNA(VLOOKUP(TEXTSPLIT(br," ",,1),h,2,0),"")))))
Dynamic array formulas.

 

[Mark McInerney]

Hi Fluff,

It is working fine (some formula!)- is there a way to tweak it to allow for two words to be checked.

In the dataset above if Cell B3 is not "A" but rather "A Z", Cell H3 is not populating, when I need it to?

Thanks - Mark.

 

[Fluff]

How about

=LET(h,HSTACK(TOCOL(B3:E9,1),TOCOL(IF(B3:E9="",x,B2:E2),2)),BYROW(G3:G6,LAMBDA(br,IFNA(VLOOKUP(br,h,2,0),CONCAT(IFNA(VLOOKUP(TEXTSPLIT(br," ",,1),h,2,0),""))))))