lookup with no particular resulting vector

[jim.knipe]

I have a chart, set up as follows

Range A12:A13 is named MANUFACTURERS
Range C12:C17 is named KAWNEER
Range D12:D14 is named VISTAWALL
Range F12:F40 is named _1600_SYSTEM_1
Range G12:G40 is named _1600_SYSTEM_1_Parts_Desc
Range I12:I40 is named _1600_SYSTEM_2
Range J12:J40 is named _1600_SYSTEM_2_Parts_Desc

Cell C2 is a data validation list =MANUFACTURERS
Cell C3 is a data validation list =INDIRECT(C2)
Cell C4 is a data validation list =INDIRECT(C3)

Cell C5 is where I have a problem. I need C5 to automatically reference the part description adjacent to the value selected in the data validation list in cell C4. In the spreadsheet below, I need cell C5 to autofill in with "PART 009".

this spreadsheet is not complete. It only shows a small portion of what the final workbook will look like. It is safe to assume there will be more than 100 more named ranges that will be options in the data validation lists.

Is it possible to have Cell C4 find the cell referenced in C3, then input the value of the adjacent cell into C5?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
2		MANUFACTURER:	KAWNEER
3		SYSTEM:	_1600_SYSTEM_1
4		PART:	162-009
5		PART DESC:
6
7
8
9
10
11	MANUFACTURERS		KAWNEER	VISTAWALL		_1600_SYSTEM_1	_1600_SYSTEM_1_PARTS_DESC		_1600_SYSTEM_2	_1600_SYSTEM_2_PARTS_DESC
12	KAWNEER		_1600_SYSTEM_1	FG_2000		162-001	PART 001		162-101	PART 101
13	VISTAWALL		_1600_SYSTEM_2	FG_3000		162-002	PART 002		162-102	PART 102
14			TRIFAB_400	FG_3000T		162-003	PART 003		162-103	PART 103
15			TRIFAB_450			162-004	PART 004		162-104	PART 104
16			TRIFAB_451			162-005	PART 005		162-105	PART 105
17			TRIFAB_451T			162-006	PART 006		162-106	PART 106
18						162-007	PART 007		162-107	PART 107
19						162-008	PART 008		162-108	PART 108
20						162-009	PART 009		162-109	PART 109
21						162-010	PART 010		162-110	PART 110
22						162-011	PART 011		162-111	PART 111
23						162-012	PART 012		162-112	PART 112
24						162-013	PART 013		162-113	PART 113
25						162-014	PART 014		162-114	PART 114
26						162-015	PART 015		162-115	PART 115
27						162-016	PART 016		162-116	PART 116
28						162-017	PART 017		162-117	PART 117
29						162-018	PART 018		162-118	PART 118
30						162-019	PART 019		162-119	PART 119
31						162-020	PART 020		162-120	PART 120
32						162-021	PART 021		162-121	PART 121
33						162-022	PART 022		162-122	PART 122
34						162-023	PART 023		162-123	PART 123
35						162-024	PART 024		162-124	PART 124
36						162-025	PART 025		162-125	PART 125
37						162-026	PART 026		162-126	PART 126
38						162-027	PART 027		162-127	PART 127
39						162-028	PART 028		162-128	PART 128
40						162-029	PART 029		162-129	PART 129
41						162-030	PART 030		162-130	PART 130
42						162-031	PART 031		162-131	PART 131
43						162-032	PART 032		162-132	PART 132
44						162-033	PART 033		162-133	PART 133
45						162-034	PART 034		162-134	PART 134
46						162-035	PART 035		162-135	PART 135
47						162-036	PART 036		162-136	PART 136
48						162-037	PART 037		162-137	PART 137
49						162-038	PART 038		162-138	PART 138
50						162-039	PART 039		162-139	PART 139
Sheet1

 

[eric_eric]

Interested to see how this turned out, any chance of sharing it?

 

[Fluff]

Did you see post#10?

 

[eric_eric]

Yes, I saw the post which provided the formula the OP needed, but I did not see finished spreadsheet.

 

[Fluff]

You can simply copy/paste the data in post#7 to create a sheet.