Find cell value using multiple criteria and a header row that does not always have a value

[Section82]

Hi everyone, I've been racking my brain and many google searches later and have been having a really hard time at this. I would really appreciate some assistance!

I've got a file that has a layout like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Product Country 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Product Country 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product Code 1[/TD]
[TD]Product Code 2[/TD]
[TD]Product Code 3[/TD]
[TD][/TD]
[TD]Product Code 5[/TD]
[TD]Product code 4[/TD]
[TD]Product code 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product Line 1[/TD]
[TD]price 1[/TD]
[TD]Price 2[/TD]
[TD]price 3[/TD]
[TD]FILLER[/TD]
[TD]Price 7[/TD]
[TD]Price 7[/TD]
[TD]price 16[/TD]
[/TR]
[TR]
[TD]Product Line 2[/TD]
[TD]Price 4[/TD]
[TD]Price 5[/TD]
[TD]Price 10[/TD]
[TD]FILLER[/TD]
[TD]Price 8[/TD]
[TD]Price 9[/TD]
[TD]price 17[/TD]
[/TR]
[TR]
[TD]Product Line 3[/TD]
[TD]price 11[/TD]
[TD]price 12[/TD]
[TD]price 15[/TD]
[TD]FILLER[/TD]
[TD]price 13[/TD]
[TD]price 14[/TD]
[TD]price 18[/TD]
[/TR]
</tbody>[/TABLE]


I am trying to extract the price from the table given an input of the country, the code, and the product line. My problem is that there can be duplicate codes and there are no other unique identifiers. I could lookup successfully if the country was present at every single column top but unfortunately its not. The country will always be either directly above the code or the first text value to the left. I'm almost positive this has to be done in VBA, but my skills are not quite there yet



Anyone have any clue how to tackle this one?

 

[DanteAmor]

Try this formula.

The criteria are in cells A14, B14 and C14.
The formula in cell D14

<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:129.27px;" /><col style="width:119.76px;" /><col style="width:120.71px;" /><col style="width:103.6px;" /><col style="width:42.77px;" /><col style="width:124.51px;" /><col style="width:121.66px;" /><col style="width:110.26px;" /></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 > </td><td >Product Country 1</td><td > </td><td > </td><td > </td><td style="background-color:#8db4e3; ">Product Country 2</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td >Product Code 1</td><td >Product Code 2</td><td >Product Code 3</td><td > </td><td >Product Code 5</td><td >Product code 4</td><td style="background-color:#ffc000; ">Product code 1</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 > </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; " >4</td><td >Product Line 1</td><td >price 1</td><td >Price 2</td><td >price 3</td><td >FILLER</td><td >Price 7</td><td >Price 7</td><td >price 16</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#b2a1c7; ">Product Line 2</td><td >Price 4</td><td >Price 5</td><td >Price 10</td><td >FILLER</td><td >Price 8</td><td >Price 9</td><td style="background-color:#92d050; ">price 17</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Product Line 3</td><td >price 11</td><td >price 12</td><td >price 15</td><td >FILLER</td><td >price 13</td><td >price 14</td><td >price 18</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </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 > </td><td > </td><td > </td><td > </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; " >9</td><td > </td><td > </td><td > </td><td > </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; " >10</td><td > </td><td > </td><td > </td><td > </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; " >11</td><td > </td><td > </td><td > </td><td > </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; " >12</td><td > </td><td > </td><td > </td><td > </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; " >13</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">COUNTRY</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">CODE</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">LINE</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">RESULT</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; " >14</td><td style="background-color:#8db4e3; ">Product Country 2</td><td style="background-color:#ffc000; ">Product code 1</td><td style="background-color:#b2a1c7; ">Product Line 2</td><td style="background-color:#92d050; ">price 17</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></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 >D14</td><td >=HLOOKUP(B14,INDEX(2:2,MATCH(A14,1:1,0)):Z10,MATCH(C14,A4:A10,0)+2,0)</td></tr></table></td></tr></table>

 

[Section82]

Hi DanteAmor,

Thanks very much for the help! I made a minor modification at the end of your formula: I figured the Z10 was the sort of outer bound that it was searching for, so I set that to well outside my dataset. I also changed the +1 instead of +2. I'm not quite sure why that worked but it did and its working beautifully. Thanks again!!

Try this formula.

The criteria are in cells A14, B14 and C14.
The formula in cell D14

	A	B	C	D	E	F	G	H
	Product Country 1
	Product Code 1	Product Code 2	Product Code 3		Product Code 5	Product code 4
Product Line 1	price 1	Price 2	price 3	FILLER	Price 7	Price 7	price 16
Price 4	Price 5	Price 10	FILLER	Price 8	Price 9
Product Line 3	price 11	price 12	price 15	FILLER	price 13	price 14	price 18

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:129.27px;"><col style="width:119.76px;"><col style="width:120.71px;"><col style="width:103.6px;"><col style="width:42.77px;"><col style="width:124.51px;"><col style="width:121.66px;"><col style="width:110.26px;"></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: #8db4e3"]Product Country 2[/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: #ffc000"]Product code 1[/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: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/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: #b2a1c7"]Product Line 2[/TD]

[TD="bgcolor: #92d050"]price 17[/TD]

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

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

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

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

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

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

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

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="bgcolor: #8db4e3"]Product Country 2[/TD]
[TD="bgcolor: #ffc000"]Product code 1[/TD]
[TD="bgcolor: #b2a1c7"]Product Line 2[/TD]
[TD="bgcolor: #92d050"]price 17[/TD]

</tbody>

Cell Formula D14 =HLOOKUP(B14,INDEX(2:2,MATCH(A14,1:1,0)):Z10,MATCH(C14,A4:A10,0)+2,0) <tbody> </tbody>

<tbody>
</tbody>

 

[DanteAmor]

Hi DanteAmor,

Thanks very much for the help! I made a minor modification at the end of your formula: I figured the Z10 was the sort of outer bound that it was searching for, so I set that to well outside my dataset. I also changed the +1 instead of +2. I'm not quite sure why that worked but it did and its working beautifully. Thanks again!!

That's right, the Z10 is the final cell.


In my example, row 2 contains the codes, and row 3 is empty, so add 2 to start with the prices.