Formula or VBA to lookup/index/match criteria

Melimob

Active Member
Joined
Oct 16, 2011
Messages
396
Office Version
  1. 365
Hi

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

I have a table with data set
[TABLE="width: 997"]
<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]CompanyID[/TD]
[TD]CompanyName[/TD]
[TD]Field Name[/TD]
[TD]Control Centre Position[/TD]
[TD]Field Source[/TD]
[TD]Country Code[/TD]
[TD]Country Name[/TD]
[TD]GDS[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]ABC COMPANY[/TD]
[TD]Cost Centre[/TD]
[TD]1[/TD]
[TD]Traveller[/TD]
[TD]ZW[/TD]
[TD]Zimbabwe[/TD]
[TD]Sabre[/TD]
[/TR]
</tbody>[/TABLE]

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)


[TABLE="width: 748"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Client[/TD]
[TD="colspan: 2"]ABC COMPANY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Current Tool[/TD]
[TD="colspan: 2"]Sabre[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]New GDS[/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]IN/OOS[/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD]Control Centre Position REF[/TD]
[TD]Standard Mapping 1[/TD]
[TD]Standard Mapping 2[/TD]
[TD]Field Name[/TD]
[TD]Field Source[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

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

Any advice gratefully received!

THANK YOU!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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 />
 
Upvote 0
Re: Formula or VBA to lkup/index/match criteria

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

Sheet BD


ABCDEFGH
ABC COMPANYCost CentreTravellerZWZimbabweSabre

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: #92d050"]CompanyID[/TD]
[TD="bgcolor: #92d050"]CompanyName[/TD]
[TD="bgcolor: #92d050"]Field Name[/TD]
[TD="bgcolor: #92d050"]Control Centre Position[/TD]
[TD="bgcolor: #92d050"]Field Source[/TD]
[TD="bgcolor: #92d050"]Country Code[/TD]
[TD="bgcolor: #92d050"]Country Name[/TD]
[TD="bgcolor: #92d050"]GDS[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]12345[/TD]

[TD="align: right"]1[/TD]

</tbody>




Sheet1

ABCDE
ABC COMPANY
Sabre
Cost CentreTraveller

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: #ffff00"]Client[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: #ffff00"]Current Tool[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: #ffff00"]New GDS[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: #ffff00"]IN/OOS[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="bgcolor: #92d050"]Control Centre Position REF[/TD]
[TD="bgcolor: #92d050"]Standard Mapping 1[/TD]
[TD="bgcolor: #92d050"]Standard Mapping 2[/TD]
[TD="bgcolor: #92d050"]Field Name[/TD]
[TD="bgcolor: #92d050"]Field Source[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]3[/TD]

</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]]))))
 
Upvote 0
Re: Formula or VBA to lkup/index/match criteria

Try :

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]D6[/TD]
[TD]=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)[/TD]
[/TR]
[TR]
[TD]E6[/TD]
[TD]=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)[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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