lookup with no particular resulting vector

J

jim.knipe

New Member
Joined
Feb 25, 2010
Messages
22
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
ABCDEFGHIJ
2MANUFACTURER:KAWNEER
3SYSTEM:_1600_SYSTEM_1
4PART:162-009
5PART DESC:
6
7
8
9
10
11MANUFACTURERSKAWNEERVISTAWALL_1600_SYSTEM_1_1600_SYSTEM_1_PARTS_DESC_1600_SYSTEM_2_1600_SYSTEM_2_PARTS_DESC
12KAWNEER_1600_SYSTEM_1FG_2000162-001PART 001162-101PART 101
13VISTAWALL_1600_SYSTEM_2FG_3000162-002PART 002162-102PART 102
14TRIFAB_400FG_3000T162-003PART 003162-103PART 103
15TRIFAB_450162-004PART 004162-104PART 104
16TRIFAB_451162-005PART 005162-105PART 105
17TRIFAB_451T162-006PART 006162-106PART 106
18162-007PART 007162-107PART 107
19162-008PART 008162-108PART 108
20162-009PART 009162-109PART 109
21162-010PART 010162-110PART 110
22162-011PART 011162-111PART 111
23162-012PART 012162-112PART 112
24162-013PART 013162-113PART 113
25162-014PART 014162-114PART 114
26162-015PART 015162-115PART 115
27162-016PART 016162-116PART 116
28162-017PART 017162-117PART 117
29162-018PART 018162-118PART 118
30162-019PART 019162-119PART 119
31162-020PART 020162-120PART 120
32162-021PART 021162-121PART 121
33162-022PART 022162-122PART 122
34162-023PART 023162-123PART 123
35162-024PART 024162-124PART 124
36162-025PART 025162-125PART 125
37162-026PART 026162-126PART 126
38162-027PART 027162-127PART 127
39162-028PART 028162-128PART 128
40162-029PART 029162-129PART 129
41162-030PART 030162-130PART 130
42162-031PART 031162-131PART 131
43162-032PART 032162-132PART 132
44162-033PART 033162-133PART 133
45162-034PART 034162-134PART 134
46162-035PART 035162-135PART 135
47162-036PART 036162-136PART 136
48162-037PART 037162-137PART 137
49162-038PART 038162-138PART 138
50162-039PART 039162-139PART 139
Sheet1
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
looks to me like you just want a VLOOKUP

Do you need to change which list you lookup from? If so, you might consider creating one single master list fom which to VLOOKUP, or possibly use your list names via an INDIRECT within your VLOOKUP. Let me know if Ive misunderstood something though
 
Upvote 0
I changed your formula to =INDEX(G12:G49,MATCH(C4,F12:F49,0)*MATCH(C3,F11:J11,0)), and put it in C5, not C4.

That worked well for the example I attached. But, if i change C3 to _1600_SYSTEM_2 and change C4 to 162-115, I get #N/A in C5. I need it to change to PART 115.

texasalynn said:
is this what you need?
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Tahoma,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 139px"><COL style="WIDTH: 129px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">MANUFACTURER:</TD><TD style="FONT-FAMILY: Verdana">KAWNEER</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">SYSTEM:</TD><TD style="FONT-FAMILY: Verdana">_1600_SYSTEM_1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">PART:</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">162-009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">PART DESC:</TD><TD style="FONT-FAMILY: Verdana">PART 009</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C4</TD><TD>=INDEX(G11:G49,MATCH(C3,F11:F49,0)*MATCH(C2,F10:I10,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
and with an if statement the second part of the statement can be eliminated

Excel Workbook
BC
1MANUFACTURER:KAWNEER
2SYSTEM:_1600_SYSTEM_2
3PART:162-109
4PART DESC:PART 009
Sheet1
 
Upvote 0
Still did not work quite like I need it to.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 180px"><COL style="WIDTH: 180px"><COL style="WIDTH: 180px"><COL style="WIDTH: 19px"><COL style="WIDTH: 180px"><COL style="WIDTH: 19px"><COL style="WIDTH: 113px"><COL style="WIDTH: 200px"><COL style="WIDTH: 19px"><COL style="WIDTH: 113px"><COL style="WIDTH: 200px"><COL style="WIDTH: 19px"><COL style="WIDTH: 180px"><COL style="WIDTH: 180px"><COL style="WIDTH: 19px"><COL style="WIDTH: 180px"><COL style="WIDTH: 180px"><COL style="WIDTH: 19px"><COL style="WIDTH: 180px"><COL style="WIDTH: 180px"><COL style="WIDTH: 19px"><COL style="WIDTH: 180px"><COL style="WIDTH: 180px"><COL style="WIDTH: 19px"><COL style="WIDTH: 180px"><COL style="WIDTH: 180px"><COL style="WIDTH: 19px"><COL style="WIDTH: 180px"><COL style="WIDTH: 180px"><COL style="WIDTH: 19px"><COL style="WIDTH: 180px"><COL style="WIDTH: 180px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD><TD>AB</TD><TD>AC</TD><TD>AD</TD><TD>AE</TD><TD>AF</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">MANUFACTURER:</TD><TD style="TEXT-ALIGN: left">KAWNEER</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">SYSTEM:</TD><TD style="TEXT-ALIGN: left">_1600_SYSTEM_2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">PART:</TD><TD style="TEXT-ALIGN: left">162-104</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">PART DESC:</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">MANUFACTURERS</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">KAWNEER</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">VISTAWALL</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">_1600_SYSTEM_1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">_1600_SYSTEM_1_PARTS_DESC</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">_1600_SYSTEM_2</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">_1600_SYSTEM_2_PARTS_DESC</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">TRIFAB_400</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">TRIFAB_400_PARTS_DESC</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">TRIFAB_450</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">TRIFAB_450_PARTS_DESC</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">TRIFAB_451</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">TRIFAB_451_PARTS_DESC</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">TRIFAB_451T</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">TRIFAB_451T_PARTS_DESC</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">FG_2000</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">FG_2000_PARTS_DESC</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">FG_3000</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">FG_3000_PARTS_DESC</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">FG_3000T</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">FG_3000T_PARTS_DESC</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center">KAWNEER</TD><TD> </TD><TD style="TEXT-ALIGN: center">_1600_SYSTEM_1</TD><TD> </TD><TD style="TEXT-ALIGN: center">FG_2000</TD><TD> </TD><TD style="TEXT-ALIGN: center">162-001</TD><TD style="TEXT-ALIGN: center">PART 1</TD><TD> </TD><TD style="TEXT-ALIGN: center">162-101</TD><TD style="TEXT-ALIGN: center">WIDGET 101</TD><TD> </TD><TD style="TEXT-ALIGN: center">400-001</TD><TD style="TEXT-ALIGN: center">DUMBELL 201</TD><TD> </TD><TD style="TEXT-ALIGN: center">450-001</TD><TD style="TEXT-ALIGN: center">ROCKING CHAIR 501</TD><TD> </TD><TD style="TEXT-ALIGN: center">451-001</TD><TD style="TEXT-ALIGN: center">TRASHCAN 701</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-001</TD><TD style="TEXT-ALIGN: center">DOORKNOB 950</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-001</TD><TD style="TEXT-ALIGN: center">DOORKNOB 950</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-001</TD><TD style="TEXT-ALIGN: center">DOORKNOB 950</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-001</TD><TD style="TEXT-ALIGN: center">DOORKNOB 950</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center">VISTAWALL</TD><TD> </TD><TD style="TEXT-ALIGN: center">_1600_SYSTEM_2</TD><TD> </TD><TD style="TEXT-ALIGN: center">FG_3000</TD><TD> </TD><TD style="TEXT-ALIGN: center">162-002</TD><TD style="TEXT-ALIGN: center">PART 2</TD><TD> </TD><TD style="TEXT-ALIGN: center">162-102</TD><TD style="TEXT-ALIGN: center">WIDGET 102</TD><TD> </TD><TD style="TEXT-ALIGN: center">400-002</TD><TD style="TEXT-ALIGN: center">DUMBELL 202</TD><TD> </TD><TD style="TEXT-ALIGN: center">450-002</TD><TD style="TEXT-ALIGN: center">ROCKING CHAIR 502</TD><TD> </TD><TD style="TEXT-ALIGN: center">451-002</TD><TD style="TEXT-ALIGN: center">TRASHCAN 702</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-002</TD><TD style="TEXT-ALIGN: center">DOORKNOB 951</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-002</TD><TD style="TEXT-ALIGN: center">DOORKNOB 951</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-002</TD><TD style="TEXT-ALIGN: center">DOORKNOB 951</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-002</TD><TD style="TEXT-ALIGN: center">DOORKNOB 951</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">TRIFAB_400</TD><TD> </TD><TD style="TEXT-ALIGN: center">FG_3000T</TD><TD> </TD><TD style="TEXT-ALIGN: center">162-003</TD><TD style="TEXT-ALIGN: center">PART 3</TD><TD> </TD><TD style="TEXT-ALIGN: center">162-103</TD><TD style="TEXT-ALIGN: center">WIDGET 103</TD><TD> </TD><TD style="TEXT-ALIGN: center">400-003</TD><TD style="TEXT-ALIGN: center">DUMBELL 203</TD><TD> </TD><TD style="TEXT-ALIGN: center">450-003</TD><TD style="TEXT-ALIGN: center">ROCKING CHAIR 503</TD><TD> </TD><TD style="TEXT-ALIGN: center">451-003</TD><TD style="TEXT-ALIGN: center">TRASHCAN 703</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-003</TD><TD style="TEXT-ALIGN: center">DOORKNOB 952</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-003</TD><TD style="TEXT-ALIGN: center">DOORKNOB 952</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-003</TD><TD style="TEXT-ALIGN: center">DOORKNOB 952</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-003</TD><TD style="TEXT-ALIGN: center">DOORKNOB 952</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">TRIFAB_450</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">162-004</TD><TD style="TEXT-ALIGN: center">PART 4</TD><TD> </TD><TD style="TEXT-ALIGN: center">162-104</TD><TD style="TEXT-ALIGN: center">WIDGET 104</TD><TD> </TD><TD style="TEXT-ALIGN: center">400-004</TD><TD style="TEXT-ALIGN: center">DUMBELL 204</TD><TD> </TD><TD style="TEXT-ALIGN: center">450-004</TD><TD style="TEXT-ALIGN: center">ROCKING CHAIR 504</TD><TD> </TD><TD style="TEXT-ALIGN: center">451-004</TD><TD style="TEXT-ALIGN: center">TRASHCAN 704</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-004</TD><TD style="TEXT-ALIGN: center">DOORKNOB 953</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-004</TD><TD style="TEXT-ALIGN: center">DOORKNOB 953</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-004</TD><TD style="TEXT-ALIGN: center">DOORKNOB 953</TD><TD> </TD><TD style="TEXT-ALIGN: center">451T-004</TD><TD style="TEXT-ALIGN: center">DOORKNOB 953</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

This is a more accurate example of what my data will look like. So if i change the system to Trifab_400, change the part to 400-003, I need C4 to reflect DUMBELL 203.

If I change the Manufacturer to Vistawall, The system to FG-3000T, the part to 451T-002, I need cell C4 to reflect DOORKNOB 951.
 
Upvote 0
This will work.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 115px"><COL style="WIDTH: 111px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">MANUFACTURER:</TD><TD>KAWNEER</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-WEIGHT: bold">SYSTEM:</TD><TD>TRIFAB_400</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-WEIGHT: bold">PART:</TD><TD style="TEXT-ALIGN: right">400-003</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-WEIGHT: bold">PART DESC:</TD><TD>DUMBELL 203</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C4</TD><TD>=INDEX(OFFSET(INDIRECT(C2),,1),MATCH(C3,INDIRECT(C2),0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?
 
Upvote 0
YES! that worked. Thank you so much.

Colton said:
This will work.

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"><colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 115px;"><col style="width: 111px;"></colgroup><tbody><tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"><td>
</td><td>B</td><td>C</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td><td style="font-weight: bold;">MANUFACTURER:</td><td>KAWNEER</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td><td style="font-weight: bold;">SYSTEM:</td><td>TRIFAB_400</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td><td style="font-weight: bold;">PART:</td><td style="text-align: right;">400-003</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td><td style="font-weight: bold;">PART DESC:</td><td>DUMBELL 203</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>C4</td><td>=INDEX(OFFSET(INDIRECT(C2),,1),MATCH(C3,INDIRECT(C2),0))</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Copy exactly Data Validate list cell but blank cell showing 0
Replies
8
Views
316
Peter_SSs
Peter_SSs
A
  • Solved
lookup value
Replies
4
Views
169
Fluff
Fluff
A
  • Question
data validation or specific value
Replies
2
Views
613
ajm
A
C
  • Locked
  • Question
Earned Value Formula Issue
Replies
1
Views
235
Fluff
Fluff
C
  • Locked
  • Question
Earned Value Formula Issue
Replies
1
Views
265
Fluff
Fluff

Forum statistics

Threads
1,215,418
Messages
6,124,793
Members
449,189
Latest member
kristinh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top