How do I make Index-match-match-match

R

rassten

Active Member
Joined
Aug 31, 2008
Messages
310
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
rassten,


Excel Workbook
ABCDEFGHIJKL
1TypeCarModelYearNumberIndb. ACitroenC3201019
2Indb. ACitroenC3201019Indb. BCitroenC4200729; 29; 19
3Indb. ASkodaFabia20101
4Indb. AVolkswagenPassat201019
5Indb. BFordFocus200638
6Indb. BOpelOther Opel (Zafira, Meriva, Insignia etc)20068
7Indb. BRenaultAll Renault200619
8Indb. BSeatAll Seat20066
9Indb. BSkodaOctavia200638
10Indb. BSuzukiOther Suzuki (SX4, Alto etc)200638
11Indb. BCitroenC4200729; 29; 19
12Indb. BMazdaAll Mazda200719
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))))
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
518
esben
E

Forum statistics

Threads
1,224,609
Messages
6,179,874
Members
452,949
Latest member
Dupuhini

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