Please help with formula

[Andries]

Hi

Please help me with a formula. I have two tables...the lookup data is in the first table (colums A,B and C...this data is obviously only a small part of the actual data)

Excel Workbook
	A	B	C
2	BLOCK/ROW/SEAT	SUBJECT CODE	O/B
3	w2-81-1	MNP20YT	*
4	w2-81-2	MNP20YT	*
5	w2-81-3	PTL311T	*
6	w2-81-4	PTL311T	*
7	w2-81-5	AMF40AT	*
8	w2-81-6	BMN23BD	*
9	w2-81-7	DLM501T	O/B
10	w2-81-8	DLM501T	O/B
11	w2-81-9	DLM501T	O/B
12	w2-81-10	DLM501T	O/B
13	w2-82-1	DLM501T	O/B
14	w2-82-2	DLM501T	O/B
15	w2-82-3	DLM501T	O/B
16	w2-82-4	DLM501T	O/B
17	w2-82-5	DLM501T	O/B
18	w2-82-6	DLM501T	O/B
19	w2-82-7	DLM501T	O/B
20	w2-82-8	DLM501T	O/B
21	w2-82-9	DLM501T	O/B
22	w2-82-10	DLM501T	O/B
23	w2-83-1	DLM501T	O/B
24	w2-83-2	DLM501T	O/B
25	w2-83-3	DLM501T	O/B
26	w2-83-4	DLM501T	O/B
27	w2-83-5	DLM501T	O/B
28	w2-83-6	DLM501T	O/B
29	w2-83-7	DLM501T	O/B
30	w2-83-8	DLM501T	O/B
31	w2-83-9	DLM501T	O/B
32	w2-83-10	DLM501T	O/B
data2

The second table must have the lookup formula to return the subject code in Column I and K and M. The problem that I have is if the subject code has an "O/B" next to it in Column C it must start in a new block

Excel Workbook
	H	I	J	K	L	M
2	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE
3	w2-81-1	*	w2-82-1	*	w2-83-1	*
4	w2-81-2	*	w2-82-2	*	w2-83-2	*
5	w2-81-3	*	w2-82-3	*	w2-83-3	*
6	w2-81-4	*	w2-82-4	*	w2-83-4	*
7	w2-81-5	*	w2-82-5	*	w2-83-5	*
8	w2-81-6	*	w2-82-6	*	w2-83-6	*
9	w2-81-7	*	w2-82-7	*	w2-83-7	*
10	w2-81-8	*	w2-82-8	*	w2-83-8	*
11	w2-81-9	*	w2-82-9	*	w2-83-9	*
12	w2-81-10	*	w2-82-10	*	w2-83-10	*
data2

Please look at the last table...it must be populated like this

Excel Workbook
	H	I	J	K	L	M
14	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE
15	w2-81-1	MNP20YT	w2-82-1	DLM501T - O/B	w2-83-1	DLM501T - O/B
16	w2-81-2	MNP20YT	w2-82-2	DLM501T - O/B	w2-83-2	DLM501T - O/B
17	w2-81-3	PTL311T	w2-82-3	DLM501T - O/B	w2-83-3	DLM501T - O/B
18	w2-81-4	PTL311T	w2-82-4	DLM501T - O/B	w2-83-4	DLM501T - O/B
19	w2-81-5	AMF40AT	w2-82-5	DLM501T - O/B	w2-83-5	DLM501T - O/B
20	w2-81-6	BMN23BD	w2-82-6	DLM501T - O/B	w2-83-6	DLM501T - O/B
21	w2-81-7	*	w2-82-7	DLM501T - O/B	w2-83-7	DLM501T - O/B
22	w2-81-8	*	w2-82-8	DLM501T - O/B	w2-83-8	DLM501T - O/B
23	w2-81-9	*	w2-82-9	DLM501T - O/B	w2-83-9	DLM501T - O/B
24	w2-81-10	*	w2-82-10	DLM501T - O/B	w2-83-10	DLM501T - O/B
data2

It is currently looking like this which is not correct and it must look like the above table

Excel Workbook
	H	I	J	K	L	M
14	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE
15	w2-81-1	MNP20YT	w2-82-1	DLM501T - O/B	w2-83-1	DLM501T - O/B
16	w2-81-2	MNP20YT	w2-82-2	DLM501T - O/B	w2-83-2	DLM501T - O/B
17	w2-81-3	PTL311T	w2-82-3	DLM501T - O/B	w2-83-3	DLM501T - O/B
18	w2-81-4	PTL311T	w2-82-4	DLM501T - O/B	w2-83-4	DLM501T - O/B
19	w2-81-5	AMF40AT	w2-82-5	DLM501T - O/B	w2-83-5	DLM501T - O/B
20	w2-81-6	BMN23BD	w2-82-6	DLM501T - O/B	w2-83-6	DLM501T - O/B
21	w2-81-7	DLM501T - O/B	w2-82-7	DLM501T - O/B	w2-83-7	DLM501T - O/B
22	w2-81-8	DLM501T - O/B	w2-82-8	DLM501T - O/B	w2-83-8	DLM501T - O/B
23	w2-81-9	DLM501T - O/B	w2-82-9	DLM501T - O/B	w2-83-9	DLM501T - O/B
24	w2-81-10	DLM501T - O/B	w2-82-10	DLM501T - O/B	w2-83-10	DLM501T - O/B
data2

Thank you in advance
Dries

 

[Peter_SSs]

Not sure I fully understand about the 3 blocks, but see if this is what you want. Formulas copied down.

I3: =IF(VLOOKUP(H3,$A$3:$C$300,3,0)="O/B","",VLOOKUP(H3,$A$3:$B$300,2,0))
K3: =VLOOKUP(J3,$A$3:$B$300,2,0)
M3: =VLOOKUP(L3,$A$3:$B$300,2,0)

 

[Andries]

Hi Peter

Thank you for your reply but I need 1 formula for all cells. The blocks are actually underneath each other.The formula works fine if there are no O/B's. What I need it to do is to skip a few lines (I21 to I24) and then continue to populate the subject codes with an O/B next to it in cell I25.

Hope this explains it better

Excel Workbook
	H	I
14	BLOCK/ROW/SEAT	SUBJECT CODE
15	w2-81-1	MNP20YT
16	w2-81-2	MNP20YT
17	w2-81-3	PTL311T
18	w2-81-4	PTL311T
19	w2-81-5	AMF40AT
20	w2-81-6	BMN23BD
21	w2-81-7	*
22	w2-81-8	*
23	w2-81-9	*
24	w2-81-10	*
25	w2-82-1	*
26	w2-82-2	*
27	w2-82-3	*
28	w2-82-4	*
29	w2-82-5	*
30	w2-82-6	*
31	w2-82-7	*
32	w2-82-8	*
33	w2-82-9	*
34	w2-82-10	*
35	w2-83-1	*
36	w2-83-2	*
37	w2-83-3	*
38	w2-83-4	*
39	w2-83-5	*
40	w2-83-6	*
41	w2-83-7	*
42	w2-83-8	*
43	w2-83-9	*
44	w2-83-10	*
data2

 

[Peter_SSs]

Like this?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1
2	BLOCK/ROW/SEAT	SUBJECT CODE	O/B
3	w2-81-1	MNP20YT
4	w2-81-2	MNP20YT
5	w2-81-3	PTL311T
6	w2-81-4	PTL311T
7	w2-81-5	AMF40AT
8	w2-81-6	BMN23BD
9	w2-81-7	DLM501T	O/B
10	w2-81-8	DLM501T	O/B
11	w2-81-9	DLM501T	O/B
12	w2-81-10	DLM501T	O/B
13	w2-82-1	DLM501T	O/B
14	w2-82-2	DLM501T	O/B					BLOCK/ROW/SEAT	SUBJECT CODE
15	w2-82-3	DLM501T	O/B					w2-81-1	MNP20YT
16	w2-82-4	DLM501T	O/B					w2-81-2	MNP20YT
17	w2-82-5	DLM501T	O/B					w2-81-3	PTL311T
18	w2-82-6	DLM501T	O/B					w2-81-4	PTL311T
19	w2-82-7	DLM501T	O/B					w2-81-5	AMF40AT
20	w2-82-8	DLM501T	O/B					w2-81-6	BMN23BD
21	w2-82-9	DLM501T	O/B					w2-81-7
22	w2-82-10	DLM501T	O/B					w2-81-8
23	w2-83-1	DLM501T	O/B					w2-81-9
24	w2-83-2	DLM501T	O/B					w2-81-10
25	w2-83-3	DLM501T	O/B					w2-82-1	DLM501T
26	w2-83-4	DLM501T	O/B					w2-82-2	DLM501T
27	w2-83-5	DLM501T	O/B					w2-82-3	DLM501T
28	w2-83-6	DLM501T	O/B					w2-82-4	DLM501T
29	w2-83-7	DLM501T	O/B					w2-82-5	DLM501T
30	w2-83-8	DLM501T	O/B					w2-82-6	DLM501T
31	w2-83-9	DLM501T	O/B					w2-82-7	DLM501T
32	w2-83-10	DLM501T	O/B					w2-82-8	DLM501T
33								w2-82-9	DLM501T
34								w2-82-10	DLM501T
35								w2-83-1	DLM501T
36								w2-83-2	DLM501T
37								w2-83-3	DLM501T
38								w2-83-4	DLM501T
39								w2-83-5	DLM501T
40								w2-83-6	DLM501T
41								w2-83-7	DLM501T
42								w2-83-8	DLM501T
43								w2-83-9	DLM501T
44								w2-83-10	DLM501T
VLOOKUP

Excel jeanie tip:
Not sure if you are aware that you can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board. Note that I have shown just one formula in my post because the others are just copied down

 

[Andries]

Hi Peter

I thank you. This is working good

Have a nice day

Dries

 

[Andries]

Hi Peter

I was too quick in saying it is working...although it does work it should carry on with the subject codes where it stopped...meaning that if you look at cell I20 the column H value is (w2-81-6). Cell I21 value (w2-81-7) should start at I25(w2-82-1). Currently it is returning (w2-82-1) value and not (w2-81-7)

Hope I explained it clearly.

thx

Dries

 

[Andries]

Hi All

Can anybody else maybe help?

 

[Peter_SSs]

I was too quick in saying it is working...although it does work it should carry on with the subject codes where it stopped...meaning that if you look at cell I20 the column H value is (w2-81-6). Cell I21 value (w2-81-7) should start at I25(w2-82-1). Currently it is returning (w2-82-1) value and not (w2-81-7)

I don't understand this description. For the data you posted in post #1, could you post a screen shot (without any formulas) of what results you expect in columns H:I?

 

[Andries]

Hi Peter

The code must look at table 1 and return the subject code to table 2 but when the there is a "O/B" status in column C then it should carry on in the green block for argument sake.

in the first table at cell B9 you find a subject with an O/B status...the code or function should then skip cells B9-12 because it is still part of the "81"rows and continue in cells B13 as shown in table 2



<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: 163px"><COL style="WIDTH: 140px"><COL style="WIDTH: 78px"><COL style="WIDTH: 187px"><COL style="WIDTH: 163px"><COL style="WIDTH: 140px"><COL style="WIDTH: 38px"></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></TR><TR style="HEIGHT: 36px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">BLOCK/ROW/SEAT</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">SUBJECT CODE</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">STATUS</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">BLOCK/ROW/SEAT</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">SUBJECT CODE</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">STATUS</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">MNP20YT</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">MNP20YT</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">MNP20YT</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">MNP20YT</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">PTL311T</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">PTL311T</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">PTL311T</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">PTL311T</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">AMF40AT</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">AMF40AT</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">BMN23BD</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">BMN23BD</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-7</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-8</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-8</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-9</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-9</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-10</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-10</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-8</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-8</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-9</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-9</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-10</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-10</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-1</TD><TD style="FONT-FAMILY: Arial"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-2</TD><TD style="FONT-FAMILY: Arial"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-3</TD><TD style="FONT-FAMILY: Arial"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-4</TD><TD style="FONT-FAMILY: Arial"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">DLM501T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</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

 

[Peter_SSs]

You seem to keep swapping columns and rows which makes it a bit tricky to follow. However, what you have just shown in columns E:F appears to be identical to columns H:I in my post #4. You initially said that was what you wanted, then you said it wasn't what you wanted now your last screen shot appears to again say it is what you want.

Maybe the problem in trying to explain what you want is that you have so many identical values down your columns so it is impossible to tell which "DLM501T" is being returned by the formula.

Is it as simple as this? (If not please make up some new dummy data that has all different values down column B so we can see exactly where they are turning up in column F)

Excel Workbook
	A	B	C	D	E	F
2	BLOCK/ROW/SEAT	SUBJECT CODE	O/B		BLOCK/ROW/SEAT	SUBJECT CODE
3	w2-81-1	MNP20YT			w2-81-1	MNP20YT
4	w2-81-2	MNP20YT			w2-81-2	MNP20YT
5	w2-81-3	PTL311T			w2-81-3	PTL311T
6	w2-81-4	PTL311T			w2-81-4	PTL311T
7	w2-81-5	AMF40AT			w2-81-5	AMF40AT
8	w2-81-6	BMN23BD			w2-81-6	BMN23BD
9	w2-81-7	DLM501T	O/B		w2-81-7
10	w2-81-8	DLM501T	O/B		w2-81-8
11	w2-81-9	DLM501T	O/B		w2-81-9
12	w2-81-10	DLM501T	O/B		w2-81-10
13	w2-82-1	DLM501T	O/B		w2-82-1	DLM501T
14	w2-82-2	DLM501T	O/B		w2-82-2	DLM501T
15	w2-82-3	DLM501T	O/B		w2-82-3	DLM501T
16	w2-82-4	DLM501T	O/B		w2-82-4	DLM501T
17	w2-82-5	DLM501T	O/B		w2-82-5	DLM501T
18	w2-82-6	DLM501T	O/B		w2-82-6	DLM501T
Formula