Formula or VBA to lookup/index/match criteria

Melimob

Active Member
Joined
Oct 16, 2011
Messages
365
Hi

I've been looking at tutorials but can't quite get this to work..

I have a table with data set
CompanyIDCompanyNameField NameControl Centre PositionField SourceCountry CodeCountry NameGDS
12345ABC COMPANYCost Centre1TravellerZWZimbabweSabre

<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

I need to populate Field Name & Field Source based on matches between

CompanyName & Client (cellD1)
GDS & Current Tool (cellD2)
Control Centre Position & Control Centre Position REF (column a)


ClientABC COMPANY
Current ToolSabre
New GDS
IN/OOS
Control Centre Position REFStandard Mapping 1Standard Mapping 2Field NameField Source
1
2
3
4
5
6
7
8
9
10

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

If too complex I think I can live without matching the GDS tool.

Any advice gratefully received!

THANK YOU!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Re: Formula or VBA to lkup/index/match criteria

Try this. Change sheet BD by the name of your sheet.

<table><b>Sheet BD</b></table><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:74.14px;" /><col style="width:96.95px;" /><col style="width:75.09px;" /><col style="width:146.38px;" /><col style="width:78.89px;" /><col style="width:86.5px;" /><col style="width:91.25px;" /><col style="width:39.92px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><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></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; ">CompanyID</td><td style="background-color:#92d050; ">CompanyName</td><td style="background-color:#92d050; ">Field Name</td><td style="background-color:#92d050; ">Control Centre Position</td><td style="background-color:#92d050; ">Field Source</td><td style="background-color:#92d050; ">Country Code</td><td style="background-color:#92d050; ">Country Name</td><td style="background-color:#92d050; ">GDS</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">12345</td><td >ABC COMPANY</td><td >Cost Centre</td><td style="text-align:right; ">1</td><td >Traveller</td><td >ZW</td><td >Zimbabwe</td><td >Sabre</td></tr></table> <br /><br />

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:170.14px;" /><col style="width:123.56px;" /><col style="width:123.56px;" /><col style="width:79.84px;" /><col style="width:94.1px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; ">Client</td><td >ABC COMPANY</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; ">Current Tool</td><td >Sabre</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; ">New GDS</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; ">IN/OOS</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#92d050; ">Control Centre Position REF</td><td style="background-color:#92d050; ">Standard Mapping 1</td><td style="background-color:#92d050; ">Standard Mapping 2</td><td style="background-color:#92d050; ">Field Name</td><td style="background-color:#92d050; ">Field Source</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; ">1</td><td > </td><td > </td><td >Cost Centre</td><td >Traveller</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; ">2</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; ">3</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D6</td><td >=INDEX(BD!$C$1:$C$10,SUMPRODUCT((BD!$B$1:$B$10=$E$1)*(BD!$H$1:$H$10=$E$2)*(BD!$D$1:$D$10=$A$6)*ROW(BD!C1:C10)))</td></tr><tr><td >E6</td><td >=INDEX(BD!$E$1:$E$10,SUMPRODUCT((BD!$B$1:$B$10=$E$1)*(BD!$H$1:$H$10=$E$2)*(BD!$D$1:$D$10=$A$6)*ROW(BD!C1:C10)))</td></tr></table></td></tr></table> <br /><br />
 

Melimob

Active Member
Joined
Oct 16, 2011
Messages
365
Re: Formula or VBA to lkup/index/match criteria

Try this. Change sheet BD by the name of your sheet.

Sheet BD


ABCDEFGH
1CompanyIDCompanyNameField NameControl Centre PositionField SourceCountry CodeCountry NameGDS
212345ABC COMPANYCost Centre1TravellerZWZimbabweSabre

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:74.14px;"><col style="width:96.95px;"><col style="width:75.09px;"><col style="width:146.38px;"><col style="width:78.89px;"><col style="width:86.5px;"><col style="width:91.25px;"><col style="width:39.92px;"></colgroup><tbody>
</tbody>




Sheet1

ABCDE
1 ClientABC COMPANY
2 Current ToolSabre
3 New GDS
4 IN/OOS
5Control Centre Position REFStandard Mapping 1Standard Mapping 2Field NameField Source
61 Cost CentreTraveller
72
83

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:170.14px;"><col style="width:123.56px;"><col style="width:123.56px;"><col style="width:79.84px;"><col style="width:94.1px;"></colgroup><tbody>
</tbody>

Formulas
CellFormula
D6=INDEX(BD!$C$1:$C$10,SUMPRODUCT((BD!$B$1:$B$10=$E$1)*(BD!$H$1:$H$10=$E$2)*(BD!$D$1:$D$10=$A$6)*ROW(BD!C1:C10)))
E6=INDEX(BD!$E$1:$E$10,SUMPRODUCT((BD!$B$1:$B$10=$E$1)*(BD!$H$1:$H$10=$E$2)*(BD!$D$1:$D$10=$A$6)*ROW(BD!C1:C10)))

<tbody>
</tbody>

<tbody>
</tbody>

Thank you

Thank you AGAIN Dante - amazing! I've converted it to name ranges so I can reuse however if on the Table12 (SheetBD) on your example, doesn't have a match with the reference no. which could be the case, it's putting in the "Field Name" as the answer. E.g. the header name. Is there anyway to account for this?
I tried to wrap it in an if error statement so it will put blank but it's still giving me the same result?

THANK YOU AGAIN - YOU ARE AMAZING!


=IF(INDEX(Table12[[#All],[Field Name]],SUMPRODUCT((Table12[[#All],[CompanyName]]=$D$1)*(Table12[[#All],[Control Centre Position]]=$A12)))="","",INDEX(Table12[[#All],[Field Name]],SUMPRODUCT((Table12[[#All],[CompanyName]]=$D$1)*(Table12[[#All],[Control Centre Position]]=$A12)*(Table12[[#All],[GDS]]=$D$2)*ROW(Table12[[#All],[Field Name]]))))
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Re: Formula or VBA to lkup/index/match criteria

Try :

D6=IFERROR(INDEX(BD!$C$2:$C$10,SUMPRODUCT((BD!$B$1:$B$10=$E$1)*(BD!$H$1:$H$10=$E$2)*(BD!$D$1:$D$10=$A$6)*ROW(BD!$C$1:$C$10))-1),$D$5)
E6=IFERROR(INDEX(BD!$E$2:$E$10,SUMPRODUCT((BD!$B$1:$B$10=$E$1)*(BD!$H$1:$H$10=$E$2)*(BD!$D$1:$D$10=$A$6)*ROW(BD!C1:C10))-1),$E$5)

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

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
Top