Look for column values from sheet1 in different ranges for multiple sheets and return the sheet name in which value was not found

[Ahmad84]

I am trying to look for Column C value (from c2 to the last row) from the sheet named as "MasterSheet" in other 3 sheets. My other 3 sheets are labeled as Region, Segment and Product.
The range where I want to conduct my search in Region sheet, is J2 to all the way down to last row. In Sheet Segment it is from K14 to the last row. In Sheet Product, it is from L22 to the last row.
I am looking for a result to show up in a new sheet with Column C values and a sheet name in front of it, if the value wasn't found.

 

[awoohaw]

This seems like it can be accomplished with one of the lookup functions.

But, can you use the xl2bb add-in to post sample data from each of your sheets? See the link below. People can help you much better if they don't have to recreate your problem from scratch.

 

[awoohaw]

Also, you have a repeated value in your lookup list.

 

[awoohaw]

Here is an example you can take this and adjust in your workbooks as needed:

Mr Excel Questions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	I am trying to look for Column C value (from c2 to the last row) from the sheet named as "MasterSheet" in other 3 sheets. My other 3 sheets are labeled as Region, Segment and Product. The range where I want to conduct my search in Region sheet, is J2 to all the way down to last row. In Sheet Segment it is from K14 to the last row. In Sheet Product, it is from L22 to the last row. I am looking for a result to show up in a new sheet with Column C values and a sheet name in front of it, if the value wasn't found.
2			PRODUCTS	Region	Segment	Product
3			China	JUBGJSUWFS	AFVMWYWDMZMI	MSMGRWKRVWHC
4			USA	LNJVCLIKRG	QRZJEPIAWTZP	MZILWKPNYWKS
5			Thailand	HAGJKFUSBO	OSNRGTYWQQDC	SOXVVPOMDLIQ
6			India	MFKAJKLOLI	MXUPDVRLMTFF	DUBQVRRNNRTC
7			Korea	IYERWYIZMG	BDLXSCSKACTU	VRIOYGCAUATT
8			Product A	WYCVECDMGL	THPYXAYAGBBY	LVDRPPOLRIGW
9			Test	SJUDSMVSXY	EDAOYJIMHRRE	VLEUZLFVFGND
10			GSRD	FOFMOSSLSY	BIFRHZLAXTDG	CGDSXDLQGIGZ
11			PL	FZUZIKIMKJ	DQYZJQBGMLNH	ZAPLCARFVFSS
12			PL	LPYKXIGOUI	AYPIYBUFFRCR	UKEHOSNFFADU
13			BE	LAQOSOSOLV	XXOOENXRHYLW	HGZTUXYEBPZP
14			FL	YZCGYAYZYM	FSICYUQSQEYF	DJKDNPSMIXAJ
15
16
Lookup In Other Sheets(LIOS)

Cell Formulas
Range	Formula
D3:D14	D3	=VLOOKUP(C3,'LIOS 1 E Region'!J3:K14,2,0)
E3:E14	E3	=VLOOKUP(C3, 'LIOS 2 Segment'!K15:L26,2,0)
F3:F14	F3	=VLOOKUP(C3,'LIOS 3 Product'!L23:M34,2,0)

Mr Excel Questions.xlsx
	I	J	K	L
1
2		PRODUCTS
3		China	JUBGJSUWFS
4		USA	LNJVCLIKRG
5		Thailand	HAGJKFUSBO
6		India	MFKAJKLOLI
7		Korea	IYERWYIZMG
8		Product A	WYCVECDMGL
9		Test	SJUDSMVSXY
10		GSRD	FOFMOSSLSY
11		PL	FZUZIKIMKJ
12		PL	LPYKXIGOUI
13		BE	LAQOSOSOLV
14		FL	YZCGYAYZYM
15
LIOS 1 E Region

Mr Excel Questions.xlsx
	J	K	L	M	N
1
2
3
4
5
6
7
8
9
10
11
12
13
14		PRODUCTS
15		China	AFVMWYWDMZMI
16		USA	QRZJEPIAWTZP
17		Thailand	OSNRGTYWQQDC
18		India	MXUPDVRLMTFF
19		Korea	BDLXSCSKACTU
20		Product A	THPYXAYAGBBY
21		Test	EDAOYJIMHRRE
22		GSRD	BIFRHZLAXTDG
23		PL	DQYZJQBGMLNH
24		PL	AYPIYBUFFRCR
25		BE	XXOOENXRHYLW
26		FL	FSICYUQSQEYF
LIOS 2 Segment

Mr Excel Questions.xlsx
	K	L	M	N	O
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22		PRODUCTS
23		China	MSMGRWKRVWHC
24		USA	MZILWKPNYWKS
25		Thailand	SOXVVPOMDLIQ
26		India	DUBQVRRNNRTC
27		Korea	VRIOYGCAUATT
28		Product A	LVDRPPOLRIGW
29		Test	VLEUZLFVFGND
30		GSRD	CGDSXDLQGIGZ
31		PL	ZAPLCARFVFSS
32		PL	UKEHOSNFFADU
33		BE	HGZTUXYEBPZP
34		FL	DJKDNPSMIXAJ
35
LIOS 3 Product

 

[Ahmad84]

Thank you @awoohaw! this is great, but I am trying to get this done through VBA.

 

[awoohaw]

Please update your requirements and thread title.