How do I make Index-match-match-match

[rassten]

I have this list:
Ark4

<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: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></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></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Type</TD><TD>Car</TD><TD>Model</TD><TD>Year</TD><TD>Number</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Indb. A</TD><TD>Citroen</TD><TD>C3</TD><TD style="TEXT-ALIGN: right">2010</TD><TD style="TEXT-ALIGN: right">19</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Indb. A</TD><TD>Skoda</TD><TD>Fabia</TD><TD style="TEXT-ALIGN: right">2010</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Indb. A</TD><TD>Volkswagen</TD><TD>Passat</TD><TD style="TEXT-ALIGN: right">2010</TD><TD style="TEXT-ALIGN: right">19</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Indb. B</TD><TD>Ford</TD><TD>Focus</TD><TD style="TEXT-ALIGN: right">2006</TD><TD style="TEXT-ALIGN: right">38</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Indb. B</TD><TD>Opel</TD><TD>Other Opel (Zafira, Meriva, Insignia etc)</TD><TD style="TEXT-ALIGN: right">2006</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Indb. B</TD><TD>Renault</TD><TD>All Renault</TD><TD style="TEXT-ALIGN: right">2006</TD><TD style="TEXT-ALIGN: right">19</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Indb. B</TD><TD>Seat</TD><TD>All Seat</TD><TD style="TEXT-ALIGN: right">2006</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Indb. B</TD><TD>Skoda</TD><TD>Octavia</TD><TD style="TEXT-ALIGN: right">2006</TD><TD style="TEXT-ALIGN: right">38</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Indb. B</TD><TD>Suzuki</TD><TD>Other Suzuki (SX4, Alto etc)</TD><TD style="TEXT-ALIGN: right">2006</TD><TD style="TEXT-ALIGN: right">38</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Indb. B</TD><TD>Citroen</TD><TD>C4</TD><TD style="TEXT-ALIGN: right">2007</TD><TD>29; 29; 19</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>Indb. B</TD><TD>Mazda</TD><TD>All Mazda</TD><TD style="TEXT-ALIGN: right">2007</TD><TD style="TEXT-ALIGN: right">19</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

But I need to extract some data, in this new list. I need to extract column L.
How can I do that
Ark4

<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: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Indb. A</TD><TD>Citroen</TD><TD>C3</TD><TD style="TEXT-ALIGN: right">2010</TD><TD style="TEXT-ALIGN: right">19</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Indb. B</TD><TD>Citroen</TD><TD>C4</TD><TD style="TEXT-ALIGN: right">2007</TD><TD>29; 29; 19</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

 

[hiker95]

rassten,

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Type	Car	Model	Year	Number			Indb. A	Citroen	C3	2010	19
2	Indb. A	Citroen	C3	2010	19			Indb. B	Citroen	C4	2007	29; 29; 19
3	Indb. A	Skoda	Fabia	2010	1
4	Indb. A	Volkswagen	Passat	2010	19
5	Indb. B	Ford	Focus	2006	38
6	Indb. B	Opel	Other Opel (Zafira, Meriva, Insignia etc)	2006	8
7	Indb. B	Renault	All Renault	2006	19
8	Indb. B	Seat	All Seat	2006	6
9	Indb. B	Skoda	Octavia	2006	38
10	Indb. B	Suzuki	Other Suzuki (SX4, Alto etc)	2006	38
11	Indb. B	Citroen	C4	2007	29; 29; 19
12	Indb. B	Mazda	All Mazda	2007	19
13
Ark4

The formula in cell L1 (confirmed with CTRL + SHIFT + ENTER) copied down:
=INDEX($E$2:$E$12,MATCH(1,IF($A$2:$A$12=$H1,IF($B$2:$B$12=$I1,IF($C$2:$C$12=$J1,IF($D$2:$D$12=$K1,1)),0))))

 

[rassten]

hiker95
Thank you, it works fantastic.

My next project will be to understand your formula. Especial the Match(1, ...
is something I never would have thought of.

 

[hiker95]

rassten,

Thanks.

My next project will be to understand your formula. Especial the Match(1, ...

There are a lot of formulae that I have found, that I can not explain how they work, but, they do.


I found the formula here, a site by Domenic:
http://www.xl-central.com/index.html

http://www.xl-central.com/lookup-multiple-criteria.html